oracle类似split的功能,oracle实现类似split效果

SELECT *

FROM (

SELECT t.*,

regexp_substr(t.str, '[^,]+', 1, x.n) cc

FROM (

SELECT 'a,b,c,d' str FROM dual

UNION ALL

SELECT '11,223,44' FROM dual

) t,

(SELECT ROWNUM n FROM dual CONNECT BY ROWNUM <= 5) x

ORDER BY 1

)

WHERE cc IS NOT NULL

注：在上述sql中，ROWNUM <= 后面的数字需要尽量取大点，要超过字符串中最多的逗号数(如果最多的逗号个数为3，则需要ROWNUM<=4)，否则会少结果。

方法二

WITH t AS(

SELECT wm_concat(tt.str) str

FROM (

SELECT 'a,b,c,d' str FROM dual

UNION ALL

SELECT '11,223,44' FROM dual

) tt

)

SELECT t.str, regexp_substr(t.str,'[^,]+',1,ROWNUM) VALUE

FROM t

CONNECT BY ROWNUM <= LENGTH(regexp_replace(t.str,'[^,]+'))+1;