WITH TAB AS (
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
select * from TAB
怎么把这两行数据已逗号为分割符,分隔成多行
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
select * from TAB
怎么把这两行数据已逗号为分割符,分隔成多行
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
select regexp_substr(str,'[^,]+',1,1) ,
regexp_substr(str,'[^,]+',1,2) ,
regexp_substr(str,'[^,]+',1,3)
...
from TAB;
WITH TAB AS (
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
select regexp_substr(str,'[^,]',1,rn) from TAB ,(select level rn from dual connect by level <=(select max(regexp_count(str,'[^,]')) from tab)) b
where regexp_substr(str,'[^,]',1,rn) is not null
WITH TAB AS (
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
SELECT REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) A
FROM (SELECT WMSYS.WM_CONCAT(STR) STR FROM TAB) TAB
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(STR, '[^,]+')) + 1
select '0,2,1,3,4,5,6,7,8,9' STR from DUAL )
SELECT regexp_substr(str,'[^,]+',1,LEVEL) FROM tab CONNECT BY LEVEL<=regexp_count(str,',')+1
像这样的结果 多行怎么处理,请大神指点下,谢谢
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
select regexp_substr(a.str1,'[^,]',1,rn)
from (select wmsys.wm_concat(str) str1 from tab) a ,
(select level rn from dual connect by level <=(select max(regexp_count(str1,'[^,]')) from (select wmsys.wm_concat(str) str1 from tab))) b
where regexp_substr(a.str1,'[^,]',1,rn) is not null结果如下:
select '0,1,2,3,4,5,6,7,8,9' STR from DUAL UNION ALL
select 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z' STR from DUAL
)
select regexp_substr(a.str1,'[^,]',1,rn)
from (select wmsys.wm_concat(str) str1 from tab) a ,
(select level rn from dual connect by level <=(select max(regexp_count(str1,'[^,]')) from (select wmsys.wm_concat(str) str1 from tab))) b
where regexp_substr(a.str1,'[^,]',1,rn) is not null结果如下:
你这种效率有点低吧,遇到行数多了那不卡死,还有类型变成了clob,最开始我也这样考虑过。应该不是最优
谢谢!