Oracle Database Splitting Delimited Strings Splitting Delimited Strings using XMLTable
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,
SUBSTR( x.item.getStringVal(), 2 ) AS item,
x.lvl
FROM table_name t
CROSS JOIN
XMLTABLE(
( '"#' || REPLACE( t.list, ',', '","#' ) || '"' )
COLUMNS item XMLTYPE PATH '.',
lvl FOR ORDINALITY
) x;
(Note: the # character is appended to facilitate extracting NULL values; it is later removed using SUBSTR( item, 2 ). If NULL values are not required then you can simplify the query and omit this.)
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
