regex - 正则表达式递归替换（增加缩进）

Question

我确定这应该是可能的，我想避免使用一个函数，因为它是页面上的一次性报告（Oracle 11g），但我真的很想用换行符格式化以下字符串，并为每个后续的缩进替换“ - ”以显示层次结构

举个例子：

This is the base level - This is level 2 - And this is the second

所以，我可以开始并用换行符替换“ - ”并应用 CSS pre 以确保它们都在一行上

但理想情况下，我想要这样：

This is the base level
> This is level 2
>> And this is the second

提前谢谢了

标签： regexoracleoracle11g

Answer 1

这有点丑陋，但有点作品（至少，我是如何理解这个问题的）。查看代码中的注释。

SQL> WITH test (col)
  2    -- Sample string
  3       AS (SELECT 'This is the base level - This is level 2 - And this is the second'
  4             FROM DUAL),
  5    -- Split it to rows (minus sign is a delimiter)
  6       temp
  7       AS (    SELECT LEVEL lvl,
  8                      TRIM (REGEXP_SUBSTR (col,
  9                                           '[^-]+',
 10                                           1,
 11                                           LEVEL))
 12                         val
 13                 FROM test
 14           CONNECT BY LEVEL <= REGEXP_COUNT (col, '-') + 1)
 15  -- join it back, using '>' along with CHR(10) as delimiter
 16  SELECT REPLACE (
 17            XMLAGG (XMLELEMENT (e, LPAD ('>', lvl - 1, '>') || val || CHR (10)) ORDER BY
 18                                                                                   lvl).EXTRACT (
 19               '//text()'),
 20            '&gt;',
 21            '>')
 22            result
 23    FROM temp;

RESULT
--------------------------------------------------------------------------------
This is the base level
>This is level 2
>>And this is the second


SQL>

---

如果你问“为什么XMLAGG而不是LISTAGG”，那是因为

 16  SELECT LISTAGG (val, LPAD ('>', lvl - 1, '>')) WITHIN GROUP (ORDER BY lvl)
 17    FROM temp;
SELECT LISTAGG (val, LPAD ('>', lvl - 1, '>')) WITHIN GROUP (ORDER BY lvl)
                                *
ERROR at line 16:
ORA-30496: Argument should be a constant.