Oracle-SQL查询实现：将$包裹的字母替换为前一位字母的方法问询

实现思路 #

• 精准匹配所有$单个大写字母$格式的子串
• 对匹配到的大写字母取ASCII码减1，转换为字符后重新用$包裹替换原内容
• 无需处理$A$边界异常（题目已明确不会出现该场景），$Z$会自动转换为$Y$符合要求

方案1：兼容Oracle 11g及以上全版本（推荐） #

使用递归CTE逐次替换所有匹配项，兼容性最强：

WITH recursive_cte AS (
    -- 初始加载待处理数据，定位第一个匹配项位置
    SELECT 
        待处理列名 AS 原始字符串,
        待处理列名 AS 当前处理字符串,
        REGEXP_INSTR(待处理列名, '\$[A-Z]\$', 1, 1, 0, 'c') AS 匹配起始位置
    FROM 你的表名
    UNION ALL
    -- 递归替换每一个匹配项
    SELECT 
        原始字符串,
        -- 拼接替换后的内容：前半段 + 替换后的$<前一位字母>$ + 后半段
        SUBSTR(当前处理字符串, 1, 匹配起始位置 - 1) 
        || '$' || CHR(ASCII(SUBSTR(当前处理字符串, 匹配起始位置 + 1, 1)) - 1) || '$'
        || SUBSTR(当前处理字符串, 匹配起始位置 + 3),
        -- 定位下一个匹配项位置
        REGEXP_INSTR(当前处理字符串, '\$[A-Z]\$', 匹配起始位置 + 3, 1, 0, 'c')
    FROM recursive_cte
    WHERE 匹配起始位置 > 0
)
-- 取最终处理完成的结果
SELECT 原始字符串, 当前处理字符串 AS 处理后结果
FROM recursive_cte
WHERE 匹配起始位置 = 0;

测试样例 #

输入字符串：'测试$F$内容$Z$结束'，预期输出：'测试$E$内容$Y$结束'
测试SQL如下：

WITH test_data AS (
    SELECT '测试$F$内容$Z$结束' AS str FROM DUAL
),
recursive_cte AS (
    SELECT 
        str AS 原始字符串,
        str AS 当前处理字符串,
        REGEXP_INSTR(str, '\$[A-Z]\$', 1, 1, 0, 'c') AS 匹配起始位置
    FROM test_data
    UNION ALL
    SELECT 
        原始字符串,
        SUBSTR(当前处理字符串, 1, 匹配起始位置 - 1) 
        || '$' || CHR(ASCII(SUBSTR(当前处理字符串, 匹配起始位置 + 1, 1)) - 1) || '$'
        || SUBSTR(当前处理字符串, 匹配起始位置 + 3),
        REGEXP_INSTR(当前处理字符串, '\$[A-Z]\$', 匹配起始位置 + 3, 1, 0, 'c')
    FROM recursive_cte
    WHERE 匹配起始位置 > 0
)
SELECT 原始字符串, 当前处理字符串 AS 处理后结果
FROM recursive_cte
WHERE 匹配起始位置 = 0;

方案2：Oracle 21c及以上版本简化写法 #

21c开始支持REGEXP_REPLACE的自定义函数替换逻辑，写法更简洁：

SELECT REGEXP_REPLACE(
    待处理列名,
    '\$[A-Z]\$',
    FUNCTION(match_str VARCHAR2) RETURN VARCHAR2 IS 
    BEGIN 
        RETURN '$' || CHR(ASCII(SUBSTR(match_str, 2, 1)) - 1) || '$'; 
    END,
    1,
    0,
    'c'
) AS 处理后结果
FROM 你的表名;

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