Oracle Database Splitting Delimited Strings Splitting Strings using XMLTable and FLWOR expressions
Example
This solution uses the ora:tokenize XQuery function that is available from Oracle 11.
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,
x.item,
x.lvl
FROM table_name t,
XMLTABLE(
'let $list := ora:tokenize(.,","),
$cnt := count($list)
for $val at $r in $list
where $r < $cnt
return $val'
PASSING list||','
COLUMNS
item VARCHAR2(100) PATH '.',
lvl FOR ORDINALITY
) (+) x;
Output:
ID ITEM LVL
---------- ------- ----------
1 a 1
1 b 2
1 c 3
1 d 4
2 e 1
3 (NULL) (NULL)
4 f 1
4 (NULL) 2
4 g 3
