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
)
negative regexp
-
if you want to grep anything except foo, use grep -v or negative lookahead
echo -e 'foo\nbar\nboz'|grep -P '^(?!.*foo).*$' bar boz it is just so
beautiful ...
1 month 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