Google
Information Storage and Retrieval: Split the String (SQL Puzzle 16)

Pages

Thursday, August 21, 2008

Split the String (SQL Puzzle 16)

Problem:
Given a string 'a,bb,ccc,dddd,ee' . Split it on the basis of commas by an SQL. The query should return :
a
bb
ccc
dddd
ee

Solution:

select
given_input,
substr(given_input,start_index+1,(end_index-start_index)-1)
from
(
select
given_input,
(case when rownum=1 then 0 else instr(given_input,',',1,rownum-1) end) start_index,
(case when instr(given_input,',',1,rownum)=0 then length(given_input)+1 else instr(given_input,',',1,rownum) end) end_index
from
(
select 'a,bb,ccc,dddd,eeeee' given_input from all_objects
)
where
rownum<=(length(given_input)- length(replace(given_input,',','')))+1
)

1 comment:

Gaurav Kant Goel said...

A more compact query is mentioned at :

http://sureshvaishya.blogspot.com/2008/05/splitting-string-using-oracle-sql.html