RE: sql question

From: Eugene Pipko <eugene.pipko_at_unionbay.com>
Date: Fri, 17 Apr 2009 08:42:02 -0700
Message-ID: <34DB87F47199374280ADFD2968CDBCFA87D621F903_at_MAIL01KT.seattlepacificindustries.com>

Thank you all for replies.
I will test your suggestions and let your know.

Eugene Pipko
Seattle Pacific Industries
office: 253.872.5243
cell: 206.304.7726
P Please consider the environment before printing this e-mail.

From: Rumpi Gravenstein [mailto:rgravens_at_gmail.com] Sent: Thursday, April 16, 2009 6:32 PM
To: jack_at_vanzanen.com
Cc: Eugene Pipko; oracle-l-freelists
Subject: Re: sql question

Jack provide the following suggestion if one knows that there can never be more than 2

<snip>
 select hts_code, substr(hts_desc,1,instr(hts_desc,'&','1')-2) from hts union all
select hts_code, substr(hts_desc,instr(hts_desc,'&','1')+2) from hts </snip>

If you are going to parse, the Oracle regular expression functions should be used For instance the following function

REGEXP_SUBSTR( hts_desc, '[^&]+',1,1 ) parses everything in the string up to the first ampersand while

while

REGEXP_SUBSTR( hts_desc, '[^&]+',1,2 ) parses from the first ampersand on to the second one

e.g.

SELECT '{'||regexp_substr('6109.10.0027 & 6205.20.2061','[^&]+',1,1 )||'}' parse1,

       '{'||regexp_substr('6109.10.0027 & 6205.20.2061','[^&]+',1,2 )||'}' parse2 from dual;

PARSE1 PARSE2
--------------- ---------------

{6109.10.0027 } { 6205.20.2061}

--

Rumpi Gravenstein

--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 17 2009 - 10:42:02 CDT