Re: sql question
- From: Rumpi Gravenstein <rgravens@xxxxxxxxx>
- To: jack@xxxxxxxxxxxx
- Date: Thu, 16 Apr 2009 21:32:11 -0400
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
Other related posts:
