===========================================================

Convert to rows by comma【By hmxxyy】

作者: lastwinner(http://lastwinner.itpub.net)
发表于: 2005.11.26 16:49
分类: Oracle ,
出处: http://lastwinner.itpub.net/post/7102/46950
---------------------------------------------------------------

SQL> select c2 from test;

C2
----------------------------------------
a,b,c,dd,efg
h,iii

select decode(lvl, 1, substr(c2, 1, instr(c2, ',', 1) - 1),
length(c2) - length(replace(c2, ',')) + 1, substr(c2, instr(c2, ',', 1, lvl-1)+1),
substr(c2, instr(c2, ',', 1, lvl-1) + 1, instr(c2, ',', 1, lvl) - instr(c2, ',', 1, lvl-1) -1))
what_you_need
from
( select level lvl
from (select data_length from user_tab_columns where table_name = 'TEST'
and column_name = 'C2')
connect by 1=1
and level <= data_length
),
test
where lvl < length(c2) - length(replace(c2, ',')) + 2
/

WHAT_YOU_NEED
-------------
a
b
c
dd
efg
h
iii

Written on 2005/03/13

稍微改善了一下，这回不用参照user_tab_columns

select
decode(lvl, 1, substr(c2, 1, instr(c2, ',', 1) - 1),
length(c2) - length(replace(c2, ',')) + 1, substr(c2, instr(c2, ',', 1, lvl-1)+1),
substr(c2, instr(c2, ',', 1, lvl-1) + 1, instr(c2, ',', 1, lvl) - instr(c2, ',', 1, lvl-1) -1))
what_you_need
from
(
select level lvl from (select max(length(c2) - length(replace(c2, ','))) + 1 maxlvl from test)
connect by 1 = 1 and level <= maxlvl
),
test
where lvl < length(c2) - length(replace(c2, ',')) + 2
/

俺狗尾续貂一个

select substr(c2,st+1,en-st-1) what_you_want from (select c2, decode(rn,1,0,instr(c2,',',1,rn-1)) st , decode(instr(c2,',',1,rn),0,length(c2)+1, instr(c2,',',1,rn)) en from (select distinct c2, level rn from test connect by level<=length(c2)-length(replace(c2,',',''))+1))
/

如果没有分隔符，比如'abc'，hmxxyy的sql会取出null，偶的避免了这个问题:)

这个方法好啊，可以用来解决http://blog.itpub.net/post/7102/42711

lastwinner 发表于:2005.11.26 16:49 ::分类: ( Oracle , ) ::阅读:(2301次) :: 评论 (5)