如何在regexp_replace函数中使用CASE语句实现条件替换？

问题背景 #

尝试在Oracle的regexp_replace函数中结合CASE语句，根据不同运算符场景去除表达式中的多余括号，但原代码中的捕获组引用'\\4'被当作普通字符串处理，导致替换未生效。

原SQL代码 #

col str for a15
with temp as
(
select 'A*((B*C)+D)' expre1 from dual union all
select 'A*((B*C)-D)' expre1 from dual union all
select '((A*B)+C)*D' expre1 from dual union all
select '((A*B)+C)+D' expre1 from dual union all
select 'A*((B+C)+D)' expre1 from dual union all
select '((A*B)+C)-D' expre1 from dual union all
select 'A*((B+C)-D)' expre1 from dual union all
select '((A*B)+C)/D' expre1 from dual union all
select '((A*B)-C)*D' expre1 from dual union all
select '((A*B)-C)+D' expre1 from dual union all
select 'A*((B-C)+D)' expre1 from dual union all
select 'A*((B-C)-D)' expre1 from dual union all
select '((A*B)-C)-D' expre1 from dual union all
select '((A*B)-C)/D' expre1 from dual union all
select 'A*((B/C)+D)' expre1 from dual union all
select 'A*((B/C)-D)' expre1 from dual union all
select 'A+((B*C)+D)' expre1 from dual union all
select 'A+((B*C)-D)' expre1 from dual union all
select '((A+B)+C)*D' expre1 from dual union all
select 'A+((B+C)+D)' expre1 from dual union all
select '((A+B)+C)+D' expre1 from dual union all
select 'A+((B+C)-D)' expre1 from dual union all
select '((A+B)+C)-D' expre1 from dual union all
select '((A+B)+C)/D' expre1 from dual union all
select '((A+B)-C)*D' expre1 from dual union all
select 'A+((B-C)+D)' expre1 from dual union all
select '((A+B)-C)+D' expre1 from dual union all
select '((A+B)-C)-D' expre1 from dual union all
select 'A+((B-C)-D)' expre1 from dual union all
select '((A+B)-C)/D' expre1 from dual union all
select 'A+((B/C)+D)' expre1 from dual union all
select 'A+((B/C)-D)' expre1 from dual union all
select 'A-((B*C)+D)' expre1 from dual union all
select 'A-((B*C)-D)' expre1 from dual union all
select '((A-B)+C)*D' expre1 from dual union all
select 'A-((B+C)+D)' expre1 from dual union all
select '((A-B)+C)+D' expre1 from dual union all
select 'A-((B+C)-D)' expre1 from dual union all
select '((A-B)+C)-D' expre1 from dual union all
select '((A-B)+C)/D' expre1 from dual union all
select '((A-B)-C)*D' expre1 from dual union all
select 'A-((B-C)+D)' expre1 from dual union all
select '((A-B)-C)+D' expre1 from dual union all
select 'A-((B-C)-D)' expre1 from dual union all
select '((A-B)-C)-D' expre1 from dual union all
select '((A-B)-C)/D' expre1 from dual union all
select 'A-((B/C)+D)' expre1 from dual union all
select 'A-((B/C)-D)' expre1 from dual union all
select 'A/((B*C)+D)' expre1 from dual union all
select 'A/((B*C)-D)' expre1 from dual union all
select '((A/B)+C)*D' expre1 from dual union all
select 'A/((B+C)+D)' expre1 from dual union all
select '((A/B)+C)+D' expre1 from dual union all
select '((A/B)+C)-D' expre1 from dual union all
select 'A/((B+C)-D)' expre1 from dual union all
select '((A/B)+C)/D' expre1 from dual union all
select '((A/B)-C)*D' expre1 from dual union all
select 'A/((B-C)+D)' expre1 from dual union all
select '((A/B)-C)+D' expre1 from dual union all
select 'A/((B-C)-D)' expre1 from dual union all
select '((A/B)-C)-D' expre1 from dual union all
select '((A/B)-C)/D' expre1 from dual union all
select 'A/((B/C)+D)' expre1 from dual union all
select 'A/((B/C)-D)' expre1 from dual
)
select expre1
       ,regexp_replace(expre1,'\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/\\+\\-])([ABCD])\\)',
        case
          when '\\4' = '+' or '\\4' = '-' then '(\\1\\2\\3\\4\\5)'
          else '((\\1\\2\\3)\\4\\5)'
        end)  str
from temp
where regexp_instr(expre1,'\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/\\+\\-])([ABCD])\\)') > 0;

原执行结果 #

EXPRE1                    STR
------------------------- ---------------
A*((B*C)+D)               A*((B*C)+D)
A*((B*C)-D)               A*((B*C)-D)
((A*B)+C)*D               ((A*B)+C)*D
((A*B)+C)+D               ((A*B)+C)+D
A*((B+C)+D)               A*((B+C)+D)
((A*B)+C)-D               ((A*B)+C)-D
A*((B+C)-D)               A*((B+C)-D)

可以看到所有表达式都未被正确替换，原因是CASE语句中的'\\4'被当作字符串常量处理，而非正则表达式的捕获组引用。

解决方案 #

原因分析 #

Oracle中，regexp_replace的替换字符串参数里的\\n是正则引擎解析的捕获组，而CASE语句属于SQL层面的逻辑，无法直接在CASE的条件判断中引用正则捕获组。需要将捕获组的提取和替换逻辑分开处理。

方案一：嵌套regexp_replace分场景处理 #

通过两次regexp_replace分别匹配不同运算符的场景，实现针对性替换：

col str for a15
with temp as
(
select 'A*((B*C)+D)' expre1 from dual union all
select 'A*((B*C)-D)' expre1 from dual union all
select '((A*B)+C)*D' expre1 from dual union all
select '((A*B)+C)+D' expre1 from dual union all
select 'A*((B+C)+D)' expre1 from dual union all
select '((A*B)+C)-D' expre1 from dual union all
select 'A*((B+C)-D)' expre1 from dual union all
select '((A*B)+C)/D' expre1 from dual union all
select '((A*B)-C)*D' expre1 from dual union all
select '((A*B)-C)+D' expre1 from dual union all
select 'A*((B-C)+D)' expre1 from dual union all
select 'A*((B-C)-D)' expre1 from dual union all
select '((A*B)-C)-D' expre1 from dual union all
select '((A*B)-C)/D' expre1 from dual union all
select 'A*((B/C)+D)' expre1 from dual union all
select 'A*((B/C)-D)' expre1 from dual union all
select 'A+((B*C)+D)' expre1 from dual union all
select 'A+((B*C)-D)' expre1 from dual union all
select '((A+B)+C)*D' expre1 from dual union all
select 'A+((B+C)+D)' expre1 from dual union all
select '((A+B)+C)+D' expre1 from dual union all
select 'A+((B+C)-D)' expre1 from dual union all
select '((A+B)+C)-D' expre1 from dual union all
select '((A+B)+C)/D' expre1 from dual union all
select '((A+B)-C)*D' expre1 from dual union all
select 'A+((B-C)+D)' expre1 from dual union all
select '((A+B)-C)+D' expre1 from dual union all
select '((A+B)-C)-D' expre1 from dual union all
select 'A+((B-C)-D)' expre1 from dual union all
select '((A+B)-C)/D' expre1 from dual union all
select 'A+((B/C)+D)' expre1 from dual union all
select 'A+((B/C)-D)' expre1 from dual union all
select 'A-((B*C)+D)' expre1 from dual union all
select 'A-((B*C)-D)' expre1 from dual union all
select '((A-B)+C)*D' expre1 from dual union all
select 'A-((B+C)+D)' expre1 from dual union all
select '((A-B)+C)+D' expre1 from dual union all
select 'A-((B+C)-D)' expre1 from dual union all
select '((A-B)+C)-D' expre1 from dual union all
select '((A-B)+C)/D' expre1 from dual union all
select '((A-B)-C)*D' expre1 from dual union all
select 'A-((B-C)+D)' expre1 from dual union all
select '((A-B)-C)+D' expre1 from dual union all
select 'A-((B-C)-D)' expre1 from dual union all
select '((A-B)-C)-D' expre1 from dual union all
select '((A-B)-C)/D' expre1 from dual union all
select 'A-((B/C)+D)' expre1 from dual union all
select 'A-((B/C)-D)' expre1 from dual union all
select 'A/((B*C)+D)' expre1 from dual union all
select 'A/((B*C)-D)' expre1 from dual union all
select '((A/B)+C)*D' expre1 from dual union all
select 'A/((B+C)+D)' expre1 from dual union all
select '((A/B)+C)+D' expre1 from dual union all
select '((A/B)+C)-D' expre1 from dual union all
select 'A/((B+C)-D)' expre1 from dual union all
select '((A/B)+C)/D' expre1 from dual union all
select '((A/B)-C)*D' expre1 from dual union all
select 'A/((B-C)+D)' expre1 from dual union all
select '((A/B)-C)+D' expre1 from dual union all
select 'A/((B-C)-D)' expre1 from dual union all
select '((A/B)-C)-D' expre1 from dual union all
select '((A/B)-C)/D' expre1 from dual union all
select 'A/((B/C)+D)' expre1 from dual union all
select 'A/((B/C)-D)' expre1 from dual
)
select expre1
       -- 先处理第4个捕获组为+或-的场景，去除内层括号
       ,regexp_replace(
           regexp_replace(expre1, '\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\+\\-])([ABCD])\\)', '(\\1\\2\\3\\4\\5)'),
           -- 再处理第4个捕获组为*或/的场景，保留内层括号
           '\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/])([ABCD])\\)', '((\\1\\2\\3)\\4\\5)'
       )  str
from temp
where regexp_instr(expre1,'\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/\\+\\-])([ABCD])\\)') > 0;

方案二：用REGEXP_SUBSTR提取捕获组后在CASE中判断 #

先通过REGEXP_SUBSTR获取第4个捕获组的运算符值，再在CASE中分支处理替换逻辑：

col str for a15
with temp as
(
select 'A*((B*C)+D)' expre1 from dual union all
-- 其他数据行与原代码一致，此处省略
)
select expre1
       ,case
           -- 提取第4个捕获组的运算符，判断是否为+或-
           when regexp_substr(expre1, '\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/\\+\\-])([ABCD])\\)', 1, 1, null, 4) in ('+', '-')
           then regexp_replace(expre1, '\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\+\\-])([ABCD])\\)', '(\\1\\2\\3\\4\\5)')
           -- 处理*或/的场景
           else regexp_replace(expre1, '\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/])([ABCD])\\)', '((\\1\\2\\3)\\4\\5)')
        end  str
from temp
where regexp_instr(expre1,'\\(\\(([ABCD])([\\*/\\+\\-])([ABCD])\\)([\\*/\\+\\-])([ABCD])\\)') > 0;

执行效果 #

两种方案都能正确实现替换，例如：

• A*((B*C)+D) 会替换为 A*(B*C+D)
• ((A*B)+C)*D 保持 ((A*B)+C)*D（因为第4个运算符是*，保留内层括号）
• ((A*B)+C)+D 替换为 (A*B+C+D)

内容的提问来源于stack exchange，提问作者Yu Chen