RE: sql question

From: Eugene Pipko <>
Date: Fri, 17 Apr 2009 08:42:02 -0700
Message-ID: <>

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 [] Sent: Thursday, April 16, 2009 6:32 PM
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

 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


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


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;

--------------- ---------------

{6109.10.0027 } { 6205.20.2061}


Rumpi Gravenstein

-- Received on Fri Apr 17 2009 - 10:42:02 CDT

Original text of this message