Oracle Recursive Sub Query Factoring using the WITH Clause
Splitting a Delimited String
Sample Data:
CREATE TABLE table_name ( VALUE VARCHAR2(50) );
INSERT INTO table_name ( VALUE ) VALUES ( 'A,B,C,D,E' );
Query:
WITH items ( list, item, lvl ) AS (
SELECT VALUE,
REGEXP_SUBSTR( VALUE, '[^,]+', 1, 1 ),
1
FROM table_name
UNION ALL
SELECT VALUE,
REGEXP_SUBSTR( VALUE, '[^,]+', 1, lvl + 1 ),
lvl + 1
FROM items
WHERE lvl < REGEXP_COUNT( VALUE, '[^,]+' )
)
SELECT * FROM items;
Output:
LIST ITEM LVL
--------- ---- ---
A,B,C,D,E A 1
A,B,C,D,E B 2
A,B,C,D,E C 3
A,B,C,D,E D 4
A,B,C,D,E E 5
