Function ora:tokenize gives
ORA-19176: FORX0003: regular expression matches zero-length string
if the string doesn't contain the split pattern, so we have to add the pattern to the string to omit this error.
The function fn:tokenize avoids this problem, so I wanted to use it for split strings like 'A$B$C' with pattern "$", but with no success (11.2.0.4, 12.2.0.1, 19.0.0.0 LiveSQL).
The splitting works with other patterns, what I'm doing wrong?
--ora:tokenize works with ","
WITH xdata(id, str) AS
(SELECT 1, 'A$B$C' FROM dual UNION ALL
SELECT 2, 'A,B,C' FROM dual)
SELECT id, str, pos, tstr
FROM xdata
, XMLTABLE( 'for $c in ora:tokenize($STR,",")[.]
return $c'
PASSING ','||str AS str
COLUMNS pos FOR ordinality,
tstr VARCHAR2 (100) PATH '.') xt;
ID STR POS TSTR
------------------------
1 A$B$C 1 A$B$C
2 A,B,C 1 A
2 A,B,C 2 B
2 A,B,C 3 C
--ora:tokenize works with "$"
WITH xdata(id, str) AS
(SELECT 1, 'A$B$C' FROM dual UNION ALL
SELECT 2, 'A,B,C' FROM dual)
SELECT id, str, pos, tstr
FROM xdata
, XMLTABLE( 'for $c in ora:tokenize($STR,"\$")[.]
return $c'
PASSING '$'||str AS str
COLUMNS pos FOR ordinality,
tstr VARCHAR2 (100) PATH '.') xt;
ID STR POS TSTR
------------------------
1 A$B$C 1 A
1 A$B$C 2 B
1 A$B$C 3 C
2 A,B,C 1 A,B,C
--fn:tokenize works with ","
WITH xdata(id, str) AS
(SELECT 1, 'A$B$C' FROM dual UNION ALL
SELECT 2, 'A,B,C' FROM dual)
SELECT id, str, pos, tstr
FROM xdata
, XMLTABLE( 'for $c in fn:tokenize($STR,",")
return $c'
PASSING str AS str
COLUMNS pos FOR ordinality,
tstr VARCHAR2 (100) PATH '.') xt;
ID STR POS TSTR
------------------------
1 A$B$C 1 A$B$C
2 A,B,C 1 A
2 A,B,C 2 B
2 A,B,C 3 C
--fn:tokenize doesn't work with "$" or "\$"
WITH xdata(id, str) AS
(SELECT 1, 'A$B$C' FROM dual UNION ALL
SELECT 2, 'A,B,C' FROM dual)
SELECT id, str, pos, tstr
FROM xdata
, XMLTABLE( 'for $c in fn:tokenize($STR,"\$")
return $c'
PASSING str AS str
COLUMNS pos FOR ordinality,
tstr VARCHAR2 (100) PATH '.') xt;
ID STR POS TSTR
------------------------
1 A$B$C 1 A$B$C
2 A,B,C 1 A,B,C
btw: the editor makes me mad !