Oracle Database 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