如何利用参考表列替换数据库字段中的指定字符？

需求说明 #

将Data表Name列（nvarchar类型）中的每个字符，按照EncodingReference表定义的SourceLetter→TargetLetter对应关系替换，得到符合要求的结果集。

数据表结构与示例数据 #

• Data表

• EncodingReference表

实现方案（SQL Server） #

方案1：字符拆分+拼接法（SQL Server 2017+）

此方法通过拆分字符串为单个字符，匹配替换规则后重新拼接，逻辑直观易理解：

SELECT 
    d.Name AS OriginalName,
    STRING_AGG(ISNULL(er.TargetLetter, c.CharValue), '') AS Name
FROM Data d
-- 拆分Name为单个字符
CROSS APPLY (
    SELECT SUBSTRING(d.Name, n.Number, 1) AS CharValue
    FROM (
        SELECT TOP (LEN(d.Name)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
        FROM sys.all_columns
    ) n
) c
-- 匹配替换规则，无匹配则保留原字符
LEFT JOIN EncodingReference er ON c.CharValue = er.SourceLetter
GROUP BY d.Name
ORDER BY d.Name;

方案2：递归CTE法（兼容低版本SQL Server）

如果使用SQL Server 2016及以下版本，可采用递归CTE循环应用替换规则：

WITH ReplaceCTE AS (
    -- 初始数据：保留原始Name和初始替换值
    SELECT 
        Name AS OriginalName,
        Name AS ReplacedName,
        1 AS ReplaceStep
    FROM Data
    UNION ALL
    -- 递归执行替换，直到无匹配字符
    SELECT 
        rc.OriginalName,
        REPLACE(rc.ReplacedName, er.SourceLetter, er.TargetLetter) AS ReplacedName,
        rc.ReplaceStep + 1 AS ReplaceStep
    FROM ReplaceCTE rc
    CROSS JOIN EncodingReference er
    WHERE CHARINDEX(er.SourceLetter, rc.ReplacedName) > 0
)
-- 取每个原始Name的最终替换结果
SELECT DISTINCT
    OriginalName,
    ReplacedName AS Name
FROM ReplaceCTE
WHERE ReplaceStep = (
    SELECT MAX(ReplaceStep) 
    FROM ReplaceCTE rc2 
    WHERE rc2.OriginalName = ReplaceCTE.OriginalName
)
ORDER BY OriginalName;

结果验证 #

执行上述任一SQL，将得到期望结果：

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