17,086
社区成员
发帖
与我相关
我的任务
分享
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(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
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(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
结果如下:
[/quote]
你这种效率有点低吧,遇到行数多了那不卡死,还有类型变成了clob,最开始我也这样考虑过。应该不是最优
谢谢!
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