普通的substr在这里应该更合适:
SELECT id,
SUBSTR(
col1,
INSTR(col1,',',1,id)+1,
INSTR(col1,',',1,id+1)-INSTR(col1,',',1,id)-1
) AS res
FROM
(
WITH t AS
(
SELECT ',aa,,cc,dd'||',' as col1
FROM dual
)
SELECT col1,
LEVEL id
FROM t
CONNECT BY LEVEL<=LENGTH(col1)-
LENGTH(REPLACE(col1,','))
-1
)
ID RES
---------- ---------------
1 aa
2
3 cc
4 dd |