Oracle中REGEXP_REPLACE为何将所有数字替换为同一数值？

问题原因 #

你的查询里，REGEXP_SUBSTR仅提取第一个匹配的数字段（示例中的27），反转后得到72，随后REGEXP_REPLACE用该值替换所有数字段，导致所有数字都变成72，不符合每个数字单独反转的需求。

解决方案 #

方法1：自定义反转函数 #

先创建一个用于反转字符串的函数：

CREATE OR REPLACE FUNCTION REVERSE_STR(p_str VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN REVERSE(p_str);
END;
/

随后在REGEXP_REPLACE中调用该函数，通过反向引用传递每个匹配的数字段，实现单独反转：

SELECT REGEXP_REPLACE(
         'Rachel Street 27, Floor 42',
         '(\d+)',
         REVERSE_STR('\1')
       ) AS corrected_address
FROM dual;

执行后会对每个数字段单独反转，得到结果：Rachel Street 72, Floor 24

方法2：无函数递归拆分处理 #

如果无法创建自定义函数，可通过递归CTE拆分字符串，处理后再拼接：

WITH addr_parts AS (
  SELECT
    REGEXP_SUBSTR('Rachel Street 27, Floor 42', '[^0-9]+|\d+', 1, LEVEL) AS part,
    LEVEL AS pos
  FROM dual
  CONNECT BY REGEXP_SUBSTR('Rachel Street 27, Floor 42', '[^0-9]+|\d+', 1, LEVEL) IS NOT NULL
),
processed_parts AS (
  SELECT
    CASE
      WHEN part REGEXP '^\d+$' THEN REVERSE(part)
      ELSE part
    END AS processed_part,
    pos
  FROM addr_parts
)
SELECT LISTAGG(processed_part, '') WITHIN GROUP (ORDER BY pos) AS corrected_address
FROM processed_parts;

该方法先将地址拆分为非数字段和数字段，对数字段单独反转后重新拼接，得到同样的正确结果。

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