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
)
TIME_BUCKET group by time period
-
We all know how to sum up by year select
to_char(trunc(hiredate,'Y'),'YYYY') year, count(*) from scott.emp group by
trunc(hiredate,'Y') order by trunc(hire...
1 week ago
1 comment:
A more compact query is mentioned at :
http://sureshvaishya.blogspot.com/2008/05/splitting-string-using-oracle-sql.html
Post a Comment