Oracle正则表达式拆分，但可以解决方格

Question

我有一个字符串，例如：“ att1：val1，[txt1，txt2：txt3]：val2“
使用Oracle SQL，我想将一行分为一行＆amp;列如下：

lvl	substr2	substr3
1	att1	val1
2	[txt1，txt2：txt3]	val2

我尝试了以下代码，但是不知道如何不被逗号分开和括号之间的括号之间的值

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

您的帮助！
弗拉德

Answer 1

您可以使用正则表达式（\ [。*？\] |。*？）：（

WITH matches (value, lvl, substr1, substr2, epos) AS (
  SELECT value,
         1,
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', 1, 1, NULL, 1),
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', 1, 1, NULL, 2),
         REGEXP_INSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', 1, 1, 1)
  FROM   table_name
UNION ALL
  SELECT value,
         lvl + 1,
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', epos, 1, NULL, 1),
         REGEXP_SUBSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', epos, 1, NULL, 2),
         REGEXP_INSTR(value, '(\[.*?\]|.*?):(.*?)(,|$)', epos, 1, 1)
  FROM   matches
  WHERE  epos > 0
)
SELECT lvl,
       substr1,
       substr2
FROM   matches
WHERE  epos > 0;

。字符串函数：

WITH matches (value, lvl, spos, mpos, epos) AS (
  SELECT value,
         1,
         1,
         CASE
         WHEN SUBSTR(value, 1, 1) = '['
         THEN INSTR(value, ']:', 1) + 1
         ELSE INSTR(value, ':', 1)
         END,
         INSTR(
           value,
           ',',
           CASE
           WHEN SUBSTR(value, 1, 1) = '['
           THEN INSTR(value, ']:', 1) + 1
           ELSE INSTR(value, ':', 1)
           END
         )
  FROM   table_name
UNION ALL
  SELECT value,
         lvl + 1,
         epos + 1,
         CASE
         WHEN SUBSTR(value, epos + 1, 1) = '['
         THEN INSTR(value, ']:', epos + 1) + 1
         ELSE INSTR(value, ':', epos + 1)
         END,
         INSTR(
           value,
           ',',
           CASE
           WHEN SUBSTR(value, epos + 1, 1) = '['
           THEN INSTR(value, ']:', epos + 1) + 1
           ELSE INSTR(value, ':', epos + 1)
           END
         )
  FROM   matches
  WHERE  epos > 0
)
SELECT lvl,
       SUBSTR(value, spos, mpos - spos) AS substr1,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, mpos + 1)
       ELSE SUBSTR(value, mpos + 1, epos - mpos - 1)
       END AS substr2
FROM   matches;

对于示例数据：

CREATE TABLE table_name (value) AS
SELECT 'Att1:Val1,[Txt1,Txt2:Txt3]:Val2' FROM DUAL;

两者都输出：

lvl	substr1	substr2
1	att1	val1
2	[txt1，txt2：txt3]	val2

db＆lt;费雷尔“>在这里