SQL Server 2016相似字符串匹配求助：如何匹配近似名称？

针对你在SQL Server 2016里遇到的近似字符串匹配难题——尤其是像「Lizzards Pub - Various」和「Lizzards Pub, The - Various」这类带语序、标点差异的条目，Soundex和Difference函数确实不太够用，手动排查45000条数据更是不现实。下面是几个高效的解决方案，适合批量处理：

Levenshtein距离是衡量两个字符串差异的经典指标（需要多少次增/删/改操作才能让两个字符串一致），SQL Server没有内置这个函数，但我们可以自己实现，然后设置相似度阈值来匹配近似项。

先创建这个函数：

CREATE FUNCTION dbo.LevenshteinDistance
(
    @s1 NVARCHAR(4000),
    @s2 NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
    DECLARE @len1 INT = LEN(@s1), @len2 INT = LEN(@s2);
    DECLARE @distanceTable TABLE (i INT, j INT, distance INT);

    IF @len1 = 0 RETURN @len2;
    IF @len2 = 0 RETURN @len1;

    INSERT INTO @distanceTable
    SELECT i, 0, i FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(i) WHERE i <= @len1
    UNION ALL
    SELECT 0, j, j FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(j) WHERE j <= @len2;

    DECLARE @i INT, @j INT, @cost INT;
    SET @i = 1;
    WHILE @i <= @len1
    BEGIN
        SET @j = 1;
        WHILE @j <= @len2
        BEGIN
            SET @cost = CASE WHEN SUBSTRING(@s1, @i, 1) = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END;

            UPDATE @distanceTable
            SET distance = 
                (SELECT MIN(d) FROM (VALUES
                    ((SELECT distance FROM @distanceTable WHERE i = @i - 1 AND j = @j) + 1),
                    ((SELECT distance FROM @distanceTable WHERE i = @i AND j = @j - 1) + 1),
                    ((SELECT distance FROM @distanceTable WHERE i = @i - 1 AND j = @j - 1) + @cost)
                ) AS t(d))
            WHERE i = @i AND j = @j;

            SET @j = @j + 1;
        END
        SET @i = @i + 1;
    END

    RETURN (SELECT distance FROM @distanceTable WHERE i = @len1 AND j = @len2);
END
GO

使用的时候，可以先过滤掉长度差异过大的条目（比如长度差超过3），再计算编辑距离，这样能大幅减少计算量：

SELECT 
    t1.Id AS Id1, t1.Name AS Name1,
    t2.Id AS Id2, t2.Name AS Name2,
    dbo.LevenshteinDistance(t1.Name, t2.Name) AS Distance
FROM YourTable t1
JOIN YourTable t2 ON t1.Id < t2.Id
WHERE ABS(LEN(t1.Name) - LEN(t2.Name)) <= 3
AND dbo.LevenshteinDistance(t1.Name, t2.Name) <= 3 -- 可根据实际情况调整阈值
ORDER BY Distance;

如果你的近似字符串经常出现字符换位或者语序小调整（比如"The"的位置变化），Damerau-Levenshtein距离更合适，它还支持字符的交换操作。同样自定义函数：

CREATE FUNCTION dbo.DamerauLevenshteinDistance
(
    @s1 NVARCHAR(4000),
    @s2 NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
    DECLARE @len1 INT = LEN(@s1), @len2 INT = LEN(@s2);
    DECLARE @d TABLE (i INT, j INT, dist INT);
    DECLARE @maxDist INT = @len1 + @len2;

    INSERT INTO @d VALUES (0, 0, @maxDist);
    INSERT INTO @d SELECT i, 0, i FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(i) WHERE i <= @len1;
    INSERT INTO @d SELECT 0, j, j FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t(j) WHERE j <= @len2;

    DECLARE @i INT, @j INT, @cost INT, @lastI INT, @lastJ INT;
    DECLARE @char1 CHAR(1), @char2 CHAR(1);

    SET @i = 1;
    WHILE @i <= @len1
    BEGIN
        SET @lastI = 0;
        SET @char1 = SUBSTRING(@s1, @i, 1);
        SET @j = 1;
        WHILE @j <= @len2
        BEGIN
            SET @lastJ = 0;
            SET @char2 = SUBSTRING(@s2, @j, 1);
            SET @cost = CASE WHEN @char1 = @char2 THEN 0 ELSE 1 END;

            UPDATE @d
            SET dist = 
                (SELECT MIN(d) FROM (VALUES
                    ((SELECT dist FROM @d WHERE i = @i - 1 AND j = @j) + 1),
                    ((SELECT dist FROM @d WHERE i = @i AND j = @j - 1) + 1),
                    ((SELECT dist FROM @d WHERE i = @i - 1 AND j = @j - 1) + @cost)
                ) AS t(d))
            WHERE i = @i AND j = @j;

            IF @i > 1 AND @j > 1 AND @char1 = SUBSTRING(@s2, @j - 1, 1) AND @char2 = SUBSTRING(@s1, @i - 1, 1)
            BEGIN
                UPDATE @d
                SET dist = CASE WHEN dist > (SELECT dist FROM @d WHERE i = @lastI - 1 AND j = @lastJ - 1) + 1 THEN (SELECT dist FROM @d WHERE i = @lastI - 1 AND j = @lastJ - 1) + 1 ELSE dist END
                WHERE i = @i AND j = @j;
            END

            IF @cost = 0
            BEGIN
                SET @lastI = @i;
                SET @lastJ = @j;
            END

            SET @j = @j + 1;
        END
        SET @i = @i + 1;
    END

    RETURN (SELECT dist FROM @d WHERE i = @len1 AND j = @len2);
END
GO

用法和Levenshtein类似，阈值可以根据你的数据调整，比如设为2或3，能精准匹配语序小变动的条目。

很多近似差异来自标点、语序、冗余词（比如"The"），先做标准化处理可以大幅降低匹配难度，比如：

• 去掉所有非字母数字的字符（逗号、破折号等）
• 把"The"这类前缀移到前面（比如把「Lizzards Pub, The」改成「The Lizzards Pub」）
• 统一大小写
• 去掉冗余的空格

先写一个标准化函数：

CREATE FUNCTION dbo.StandardizeString
(
    @input NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    -- 统一转小写
    SET @input = LOWER(@input);
    -- 去掉非字母数字的字符
    SET @input = REPLACE(REPLACE(REPLACE(@input, ',', ''), '-', ''), '.', '');
    -- 处理"The"在末尾的情况
    IF RIGHT(@input, 4) = ' the'
        SET @input = 'the ' + LEFT(@input, LEN(@input) - 4);
    -- 去掉多余空格
    WHILE CHARINDEX('  ', @input) > 0
        SET @input = REPLACE(@input, '  ', ' ');
    SET @input = LTRIM(RTRIM(@input));

    RETURN @input;
END
GO

标准化之后，再用编辑距离或者直接模糊匹配，效率会高很多：

SELECT 
    t1.Id AS Id1, t1.Name AS Name1,
    t2.Id AS Id2, t2.Name AS Name2
FROM YourTable t1
JOIN YourTable t2 ON t1.Id < t2.Id
WHERE dbo.StandardizeString(t1.Name) = dbo.StandardizeString(t2.Name)
OR dbo.LevenshteinDistance(dbo.StandardizeString(t1.Name), dbo.StandardizeString(t2.Name)) <= 2;

如果你的数据量持续增长，全文索引是更高效的选择。先给你的表创建全文索引：

-- 先创建全文目录
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
-- 创建全文索引（替换PK_YourTable为你的主键索引名）
CREATE FULLTEXT INDEX ON YourTable(Name)
KEY INDEX PK_YourTable
WITH STOPLIST = SYSTEM;

然后可以用CONTAINS或者FREETEXT来查询近似匹配，还可以自定义同义词库（比如把"Lizzards"和"Lizards"映射为同义词，或者把"The"设为停用词）：

SELECT *
FROM YourTable t1
WHERE EXISTS (
    SELECT 1 FROM YourTable t2
    WHERE t1.Id != t2.Id
    AND CONTAINS(t2.Name, '"' + REPLACE(t1.Name, '-', ' ') + '"')
);

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