Thursday, December 16, 2010

convert cell to multiple rows

with t as
(select 'Simpson, Homer//Flintstone, Fred//Jetson, George' txt from dual
)
--
-- actual query #1:
--
select substr( txt
, instr (txt, '//', 1, level)+2
, instr (txt, '//', 1, level+1) - instr (txt, '//', 1, level)-2
) txt
from ( select '//'||txt||'//' txt
from t
)
connect by level <= (length(txt)-length(replace(txt,'//'))) / 2 - 1


Output
TXT
=======
Simpson, Homer
Flintstone, Fred
Jetson, George