General
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!!!