Oracle Database Splitting Delimited Strings Splitting Strings using a Correlated Table Expression
Example
Sample Data:
CREATE TABLE table_name ( id, list ) AS
SELECT 1, 'a,b,c,d' FROM DUAL UNION ALL -- Multiple items in the list
SELECT 2, 'e' FROM DUAL UNION ALL -- Single item in the list
SELECT 3, NULL FROM DUAL UNION ALL -- NULL list
SELECT 4, 'f,,g' FROM DUAL; -- NULL item in the list
Query:
SELECT t.id,
v.COLUMN_VALUE AS value,
ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS lvl
FROM table_name t,
TABLE(
CAST(
MULTISET(
SELECT REGEXP_SUBSTR( t.list, '([^,]*)(,|$)', 1, LEVEL, NULL, 1 )
FROM DUAL
CONNECT BY LEVEL < REGEXP_COUNT( t.list, '[^,]*(,|$)' )
)
AS SYS.ODCIVARCHAR2LIST
)
) v;
Output:
ID ITEM LVL
---------- ------- ----------
1 a 1
1 b 2
1 c 3
1 d 4
2 e 1
3 (NULL) 1
4 f 1
4 (NULL) 2
4 g 3
