select statement for specific pattern

[Ratzie]

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

[Victor Fridyev]

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

[Ratzie]

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]

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

HTH

[Sandman!]

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]

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 · Accepted solution]

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]

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

[Ratzie]

The substring worked purfect!!!