Oracle Database Splitting Delimited Strings Splitting Strings using CROSS APPLY (Oracle 12c)
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,
REGEXP_SUBSTR( t.list, '([^,]*)($|,)', 1, l.lvl, NULL, 1 ) AS item,
l.lvl
FROM table_name t
CROSS APPLY
(
SELECT LEVEL AS lvl
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT( t.list, ',' ) + 1
) l;
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
