topic Re: select statement for specific pattern in Operating System - HP-UX

select statement for specific pattern

Ratzie — Fri, 07 Jul 2006 11:32:00 GMT

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

Re: select statement for specific pattern

Victor Fridyev — Fri, 07 Jul 2006 12:13:48 GMT

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

Re: select statement for specific pattern

Ratzie — Fri, 07 Jul 2006 12:21:37 GMT

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

Re: select statement for specific pattern

Victor Fridyev — Fri, 07 Jul 2006 12:34:59 GMT

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

HTH

Re: select statement for specific pattern

Sandman! — Fri, 07 Jul 2006 13:16:26 GMT

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

Re: select statement for specific pattern

Ratzie — Fri, 07 Jul 2006 13:20:19 GMT

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:
## # ## ## #

Re: select statement for specific pattern

spex — Fri, 07 Jul 2006 13:28:07 GMT

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

Re: select statement for specific pattern

spex — Fri, 07 Jul 2006 13:32:49 GMT

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

Re: select statement for specific pattern

Ratzie — Tue, 11 Jul 2006 11:59:01 GMT

The substring worked purfect!!!