ITPub博客

首页 > 数字化转型 > ERP > Convert to rows by comma【By hmxxyy】

Convert to rows by comma【By hmxxyy】

原创 ERP 作者:lastwinner 时间:2005-11-26 16:49:39 0 删除 编辑

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

[@more@]

稍微改善了一下,这回不用参照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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-810747/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 今天收成不错
请登录后发表评论 登录
全部评论

注册时间:2007-12-12

  • 博文量
    223
  • 访问量
    2812814