SQL Server中使用REPLACE函数实现精确匹配的通用方案

直接使用REPLACE()函数会替换字符串中所有匹配的子串（比如示例中Courtney里的Court会被误替换），无法实现精确匹配整个单词的需求。以下是两种通用解决方案，适用于不同版本的SQL Server：

方法一：STRING_SPLIT + STRING_AGG（SQL Server 2017+） #

利用字符串拆分和聚合函数，将原字符串拆分为单个单词后逐一匹配替换，再重新拼接，确保只替换完整单词。

SELECT 
    pd.full_name,
    COALESCE(
        STRING_AGG(
            ISNULL(sn.standard_name, split.value), 
            ' '
        ),
        pd.full_name
    ) AS Standard_full_name
FROM Personal_details pd
CROSS APPLY STRING_SPLIT(pd.full_name, ' ') split
LEFT JOIN Standrd_name sn 
    ON sn.name = split.value
GROUP BY pd.full_name;

逻辑说明 #

1. 用STRING_SPLIT()将full_name按空格拆分为独立单词；
2. 每个单词左连接Standrd_name表，匹配到则替换为对应的standard_name，未匹配则保留原单词；
3. 用STRING_AGG()将替换后的单词重新拼接为完整字符串。

方法二：递归CTE（兼容低版本SQL Server） #

通过递归方式逐次替换字符串中的精确匹配单词，避免子串误替换，适合SQL Server 2016及以下版本。

WITH RecursiveReplace AS (
    SELECT 
        full_name,
        full_name AS current_name,
        0 AS iteration
    FROM Personal_details
    UNION ALL
    SELECT 
        rr.full_name,
        REPLACE(rr.current_name, sn.name, sn.standard_name),
        rr.iteration + 1
    FROM RecursiveReplace rr
    JOIN Standrd_name sn 
        ON CHARINDEX(' ' + sn.name + ' ', ' ' + rr.current_name + ' ') > 0
    WHERE rr.iteration < (SELECT COUNT(*) FROM Standrd_name) -- 限制迭代次数，防止无限递归
)
SELECT 
    full_name,
    CASE 
        WHEN EXISTS (SELECT 1 FROM Standrd_name WHERE CHARINDEX(' ' + name + ' ', ' ' + full_name + ' ') > 0)
        THEN (SELECT TOP 1 current_name FROM RecursiveReplace WHERE full_name = rr.full_name ORDER BY iteration DESC)
        ELSE full_name
    END AS Standard_full_name
FROM RecursiveReplace rr
GROUP BY full_name;

逻辑说明 #

1. 初始CTE将原字符串作为起始值；
2. 递归阶段：通过CHARINDEX(' ' + sn.name + ' ', ...)判断是否存在完整匹配的单词，存在则替换；
3. 最终取迭代次数最多的结果（即完成所有可能的替换），未匹配的字符串保留原样。

通用扩展说明 #

• 大小写不敏感匹配：若需要忽略大小写，可在JOIN条件中加入排序规则转换，例如sn.name COLLATE SQL_Latin1_General_CP1_CI_AS = split.value；
• 多分隔符处理：如果字符串包含除空格外的分隔符（如逗号、连字符），可先通过REPLACE()统一替换为空格，再进行拆分/匹配；
• 性能优化：数据量较大时，优先选择方法一，递归CTE在数据量高时可能存在性能瓶颈。

内容的提问来源于stack exchange，提问作者Dusmanta kumar rout