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;