I have a string like : "Att1:Val1,[Txt1,Txt2:Txt3]:Val2"
Using Oracle Sql, I would like to achieve a split into rows & columns as below :
lvl | substr2 | substr3 |
---|---|---|
1 | Att1 | Val1 |
2 | [Txt1,Txt2:Txt3] | Val2 |
I have tried below code, but can't figure out how not to split by comma and colon the values between the brackets
with WTBL as
(
select 'Att1:Val1,[Txt1,Txt2:Txt3]:Val2,' as WCLN
from dual
)
select lvl, substr1, substr2, substr3, WCLN
from WTBL
cross join xmltable('if (contains($PRM,","))
then
let $list := ora:tokenize($PRM, ","),
$cnt := count($list)
for $val at $r in $list
where $r < $cnt
return $val
else $PRM'
passing WCLN as PRM
columns substr1 varchar2(4000) path '.'
,substr2 varchar2(4000) path 'if (contains( . , ":")) then
let $list := ora:tokenize( . ,":"),
$cnt := count($list)
for $val at $r in $list
where $r = $cnt - 1
return $val
else . '
,substr3 varchar2(4000) path 'if (contains( . , ":")) then
let $list := ora:tokenize( . ,":"),
$cnt := count($list)
for $val at $r in $list
where $r = $cnt
return $val
else . '
,lvl FOR ORDINALITY
) xm
Your help is much appreciated!
Vlad
You can use the regular expression
(\[.*?\]|.*?):(.*?)(,|$)
and a recursive sub-query:Or, simple (faster) string functions:
Which, for the sample data:
Both output:
db<>fiddle here