Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

select statement for specific pattern

SOLVED
Go to solution
Ratzie
Super Advisor

select statement for specific pattern

I have a column that I need to select on:
Data looks like:
01 002 120
01 002 160
01 005 120
01 002 125
01 1 00 02 1
01 2 00 02 3
etc.

I need to only pull this type of format:
01 002 120

Next question:
How to update, this type of format:
01 002 120

To this type of format:
01 2 00 02 3
8 REPLIES
Victor Fridyev
Honored Contributor

Re: select statement for specific pattern

Hi,

It's not fully clear, but you if format means 3 fields and 5 fields, so:

awk 'NF==5 {print; next}
NF==3 {do_what_you_need; next}' input_file > output_file

HTH
Entities are not to be multiplied beyond necessity - RTFM
Ratzie
Super Advisor

Re: select statement for specific pattern

How can I use this with Oracle select statememt.

Bascially the format that I need to find is
Where col like ....
99 999 999
## ### ###

Not an awk statement
Victor Fridyev
Honored Contributor

Re: select statement for specific pattern

I suggest, there is a routine in Oracle, which gives number of words in record.

HTH
Entities are not to be multiplied beyond necessity - RTFM
Sandman!
Honored Contributor

Re: select statement for specific pattern

imho...the requirement is quite confusing. On one hand you are trying to compare values in a flat file to what's stored inside the Oracle database, while at the same time expanding all the 3-field records to 5-fields. Not sure here? Please clarify further.

Here's what you need to do to convert all 3-field records to 5-field records:

# sed 's/^\(..\) \(.\)\(..\) \(.\)\(..\)$/\1 \2 \3 0\4 \5/p' inputfile
Ratzie
Super Advisor

Re: select statement for specific pattern

Sorry lets try this again.
I need to run a select statement on a table in Oracle.

have a column that I need to select on:
Data looks like:
01 002 120
01 002 160
01 005 120
01 002 125
01 1 00 02 1
01 2 00 02 3
01 2 03 02 3
01 042 160
01 2 06 02 3
01 202 167
etc.

I need to only pull this type of format:
## ### ###


select col1 from table where col1 like??


Next question:
How to update, this type of format:
## ### ###


To this type of format:
## # ## ## #


spex
Honored Contributor
Solution

Re: select statement for specific pattern

Hello,

Here's a select statement that accomplishes what you're looking for (notice the use of the substr() function):

select id "id",substr(id,1,2)||' '||substr(id,4,1)||' '||
substr(id,5,2)||' '||substr(id,8,2)||' '||substr(id,10,1) "formatted id"
from tab01
where id like '__ ___ ___'
;

You might consider selecting into another table instead of updating rows in the existing table.

A link to the Oracle 9i SQL reference:

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/toc.htm

PCS
spex
Honored Contributor

Re: select statement for specific pattern

If you're using 10g, consider using Oracle Regular Expressions.
Ratzie
Super Advisor

Re: select statement for specific pattern

The substring worked purfect!!!