Oracle SQL中用regexp_replace批量替换指定三字符为对应单字符

针对你需要批量替换指定三字符字符串为对应单字符，同时保留未匹配项的需求，以下两种方案无需嵌套多层REGEXP_REPLACE，且映射规则集中管理，维护更方便：

方案一：XMLTABLE拆分+映射表匹配+LISTAGG拼接 #

该方案通过拆分字符串为三字符片段，匹配预定义的映射表后重新拼接，逻辑清晰，适配任意长度的输入字符串：

WITH triplet_map AS (
    -- 定义三字符到单字符的映射规则，新增/修改只需调整这里
    SELECT 'Ala' AS triplet, 'A' AS single FROM dual UNION ALL
    SELECT 'Arg' AS triplet, 'R' AS single FROM dual UNION ALL
    SELECT 'Glu' AS triplet, 'E' AS single FROM dual UNION ALL
    SELECT 'Lys' AS triplet, 'K' AS single FROM dual UNION ALL
    SELECT 'Ser' AS triplet, 'S' AS single FROM dual UNION ALL
    SELECT 'Thr' AS triplet, 'T' AS single FROM dual UNION ALL
    SELECT 'Ile' AS triplet, 'I' AS single FROM dual UNION ALL
    SELECT 'Leu' AS triplet, 'L' AS single FROM dual UNION ALL
    SELECT 'Met' AS triplet, 'M' AS single FROM dual
)
SELECT
    t.column_a AS original_value,
    LISTAGG(COALESCE(m.single, s.fragment), '') WITHIN GROUP (ORDER BY s.position) AS replaced_value
FROM
    your_table t,
    -- 将输入字符串拆分为三字符片段（含末尾不足3的字符）
    XMLTABLE(
        'for $i in 1 to ceiling(string-length($input_str)/3) 
         return substring($input_str, ($i-1)*3 + 1, 3)'
        PASSING t.column_a AS input_str
        COLUMNS
            fragment VARCHAR2(3) PATH '.',
            position FOR ORDINALITY
    ) s
-- 匹配映射规则，未匹配的片段保留原内容
LEFT JOIN triplet_map m ON s.fragment = m.triplet
GROUP BY t.column_a;

方案优势： #

• 映射规则集中在triplet_map中，新增或修改映射无需调整核心逻辑
• 自动处理长度不是3倍数的字符串，末尾不足3的字符会原样保留
• 避免嵌套多层替换函数，代码可读性高

方案二：递归CTE逐步替换 #

通过递归CTE遍历映射规则，逐个完成替换，适合需要严格按顺序替换的场景：

WITH triplet_map AS (
    SELECT 'Ala' AS triplet, 'A' AS single, 1 AS seq FROM dual UNION ALL
    SELECT 'Arg' AS triplet, 'R' AS single, 2 AS seq FROM dual UNION ALL
    SELECT 'Glu' AS triplet, 'E' AS single, 3 AS seq FROM dual UNION ALL
    SELECT 'Lys' AS triplet, 'K' AS single, 4 AS seq FROM dual UNION ALL
    SELECT 'Ser' AS triplet, 'S' AS single, 5 AS seq FROM dual UNION ALL
    SELECT 'Thr' AS triplet, 'T' AS single, 6 AS seq FROM dual UNION ALL
    SELECT 'Ile' AS triplet, 'I' AS single, 7 AS seq FROM dual UNION ALL
    SELECT 'Leu' AS triplet, 'L' AS single, 8 AS seq FROM dual UNION ALL
    SELECT 'Met' AS triplet, 'M' AS single, 9 AS seq FROM dual
),
recursive_replacer AS (
    -- 初始步骤：取原始值，设置最大序列数
    SELECT
        column_a AS original_value,
        column_a AS current_value,
        (SELECT MAX(seq) FROM triplet_map) AS remaining_seq
    FROM your_table
    UNION ALL
    -- 递归步骤：按序列顺序逐个替换映射项
    SELECT
        r.original_value,
        REGEXP_REPLACE(r.current_value, m.triplet, m.single),
        r.remaining_seq - 1
    FROM recursive_replacer r
    JOIN triplet_map m ON m.seq = r.remaining_seq
    WHERE r.remaining_seq > 0
)
-- 取递归完成后的最终结果
SELECT original_value, current_value AS replaced_value
FROM recursive_replacer
WHERE remaining_seq = 0;

方案优势： #

• 可通过调整seq值控制替换顺序，避免替换冲突（比如若存在重叠映射时）
• 无需拆分字符串，直接对原字符串进行替换操作

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