Re: sql question

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Thu, 16 Apr 2009 21:32:11 -0400
Message-ID: <9c9b9dc90904161832o49835574p2acb0a53a70965cf_at_mail.gmail.com>

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) fromhts
 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 Thu Apr 16 2009 - 20:32:11 CDT