SQL difference()函数处理希伯来语文本失效问题求助

我有一个包含英语和希伯来语列的SQL表，希望使用difference()函数实现模糊搜索，返回与查询词（英文或希伯来语）最相似的记录。但发现该函数仅对英文值有效，处理希伯来语值时始终返回4，导致搜索希伯来语时会返回所有符合>=4条件的行（也就是全部记录）。

示例表结构与数据 #

create table dbo.Country(
    CountryId int not null identity primary key,
    CountryEN nvarchar(50) not null 
        constraint ck_Country_CountryEN_cannot_be_blank check(CountryEN > '')
        constraint u_Country_CountryEN unique,
    CountryHE nvarchar(50) COLLATE Hebrew_CI_AS_KS_WS not null 
        constraint ck_Country_CountryHE_cannot_be_blank check(CountryHE > '')
        constraint u_Country_CountryHE unique
)

insert Country(CountryEN, CountryHE)
select 'Hungary', N'אונגארן'
union select 'Czech Republic', N'טשעכיי'
union select 'Poland', N'פוילן'

存储过程代码 #

create or alter procedure dbo.CountryGet(
    @CountryId int = 0,
    @CountryName nvarchar(50) = '',
    @All bit = 0,
    @Message varchar(500) = '' output
)
as
begin
    declare @return int = 0
    
    select co.CountryId, co.CountryEN, co.CountryHE
    from Country co
    where co.CountryId = @CountryId
    or difference(co.CountryEN,@CountryName) >= 4
    or difference(co.CountryHE,@CountryName) >= 4
    or @All = 1

    return @return
end

测试现象 #

• 测试拼写错误的英文国名：

  exec CountryGet @CountryName = 'Hunger'
  

  按预期返回1行记录（匹配Hungary）。

• 测试正确的希伯来语国名：

  exec CountryGet @CountryName = N'אונגארן'
  

  却返回所有行，因为difference(co.CountryHE,@CountryName)对所有希伯来语值都返回4。

如果改用co.CountryHE = @CountryName替代difference()可以得到精确匹配的结果，但我需要实现希伯来语的相似性匹配。

解决方案 #

SQL Server的difference()函数依赖于英语SOUNDEX算法，仅针对拉丁字母设计，对希伯来语等非拉丁文字完全不适用。要实现希伯来语的相似性匹配，可采用以下两种方案：

1. 实现Levenshtein距离（编辑距离）函数 #

Levenshtein距离通过计算两个字符串的编辑次数（插入、删除、替换）衡量相似度，适合小数据量场景：

CREATE FUNCTION dbo.LevenshteinDistance
(
    @s NVARCHAR(4000),
    @t NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
    DECLARE @len_s INT = LEN(@s), @len_t INT = LEN(@t)
    DECLARE @d TABLE (i INT, j INT, val INT)

    IF @len_s = 0 RETURN @len_t
    IF @len_t = 0 RETURN @len_s

    INSERT INTO @d
    SELECT i, 0, i FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS x(i) WHERE i <= @len_s
    UNION ALL
    SELECT 0, j, j FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS x(j) WHERE j <= @len_t

    DECLARE @i INT = 1, @j INT, @cost INT
    WHILE @i <= @len_s
    BEGIN
        SET @j = 1
        WHILE @j <= @len_t
        BEGIN
            SET @cost = CASE WHEN SUBSTRING(@s, @i, 1) = SUBSTRING(@t, @j, 1) THEN 0 ELSE 1 END

            UPDATE @d
            SET val = (SELECT MIN(val) FROM (
                SELECT val + 1 FROM @d WHERE i = @i AND j = @j - 1
                UNION ALL
                SELECT val + 1 FROM @d WHERE i = @i - 1 AND j = @j
                UNION ALL
                SELECT val + @cost FROM @d WHERE i = @i - 1 AND j = @j - 1
            ) AS temp)
            WHERE i = @i AND j = @j

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

    RETURN (SELECT val FROM @d WHERE i = @len_s AND j = @len_t)
END

修改存储过程，设置编辑距离阈值（比如允许最多2个字符差异）：

create or alter procedure dbo.CountryGet(
    @CountryId int = 0,
    @CountryName nvarchar(50) = '',
    @All bit = 0,
    @Message varchar(500) = '' output
)
as
begin
    declare @return int = 0
    
    select co.CountryId, co.CountryEN, co.CountryHE
    from Country co
    where co.CountryId = @CountryId
    or difference(co.CountryEN,@CountryName) >= 4
    or dbo.LevenshteinDistance(co.CountryHE,@CountryName) <= 2
    or @All = 1

    return @return
end

2. 希伯来语专用语音编码（适合大数据量） #

如果需要类似SOUNDEX的语音匹配效果，可以实现希伯来语专用的语音编码算法，将希伯来语值预先编码存储在表中，搜索时对查询词同步编码后匹配。这种方式性能更优，适合数据量大的场景。

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