Perfect application of regular expressions.
with i as (
select ‘,Database,10g,Oracle10,’ i from dual union all
select ‘,10g,Oracle20,’ i from dual union all
select ‘,Data,Menu,OracleAA,’ i from dual )
select regexp_substr( ‘,’ || i . i || ‘,’ , ‘.,([^,]+),’ , 1 , 1 ,null, 1
) result
, i . i
from i ;
RESULT I
Oracle10 ,Database,10g,Oracle10,
Oracle20 ,10g,Oracle20,
OracleAA ,Data,Menu,OracleAA,
– oops – I used the “subexpr” parameter of regexp_substr, which is new
with Oracle 11g. But maybe you can get (more) creative and find a way that works
in 10g?
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592