如何在SQL Server（含2008旧版本）中编写容错过滤查询

针对你需要在SQL Server端处理药品名称拼写变体（如lasix的各类输入错误）、兼容2008版本、不硬编码且不影响性能的需求，以下是几种可行方案：

1. 基于发音匹配：SOUNDEX + DIFFERENCE #

该方案通过发音相似度匹配，适合处理lisax这类发音接近的变体，同时对lasixx这类重复字符的输入也有效（SOUNDEX会忽略连续重复辅音）。

实现步骤： #

• 先为药品表添加发音编码计算列并创建索引（优化大数据量查询性能）：

ALTER TABLE medicines ADD NameSoundex AS SOUNDEX(Name);
CREATE NONCLUSTERED INDEX IX_Medicines_NameSoundex ON medicines(NameSoundex);

• 查询时使用DIFFERENCE函数判断相似度（返回值0-4，4为完全匹配，建议阈值设为3）：

DECLARE @userInput NVARCHAR(100) = 'lasax'; -- 用户输入的变体
SELECT Name 
FROM medicines 
WHERE DIFFERENCE(NameSoundex, SOUNDEX(@userInput)) >= 3;

优势： #

• 原生函数性能优异，结合索引可快速过滤数据
• 无需硬编码任何变体规则
• 完美兼容SQL Server 2008

2. 基于编辑距离：自定义Levenshtein函数 #

编辑距离（Levenshtein Distance）计算两个字符串的最少编辑操作次数（插入、删除、替换），适合处理lasi（缺字符）、lasax（字符替换）这类拼写错误。SQL Server 2008无内置函数，需自定义实现。

实现步骤： #

• 创建编辑距离计算函数：

CREATE FUNCTION dbo.LevenshteinDistance
(
    @s NVARCHAR(4000),
    @t NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
    DECLARE @d TABLE (i INT, j INT, dist INT);
    DECLARE @len_s INT = LEN(@s), @len_t INT = LEN(@t);
    
    INSERT INTO @d VALUES (0, 0, 0);
    ;WITH cte_i AS (SELECT 1 AS i UNION ALL SELECT i+1 FROM cte_i WHERE i < @len_s),
         cte_j AS (SELECT 1 AS j UNION ALL SELECT j+1 FROM cte_j WHERE j < @len_t)
    INSERT INTO @d (i, j, dist)
    SELECT i, 0, i FROM cte_i
    UNION ALL
    SELECT 0, j, j FROM cte_j;
    
    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 dist = 
                (SELECT MIN(val) 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 vals(val))
            WHERE i = @i AND j = @j;
            
            SET @j = @j + 1;
        END
        SET @i = @i + 1;
    END
    
    RETURN (SELECT dist FROM @d WHERE i = @len_s AND j = @len_t);
END

• 查询时设置合理的距离阈值（比如2，覆盖多数常见拼写错误），同时结合SOUNDEX过滤候选集避免全表扫描：

DECLARE @userInput NVARCHAR(100) = 'lasi';
SELECT Name 
FROM medicines 
WHERE DIFFERENCE(Name, @userInput) >= 3
  AND dbo.LevenshteinDistance(Name, @userInput) <= 2;

注意： #

• 直接使用自定义函数会触发全表扫描，必须先通过SOUNDEX快速缩小候选范围
• 函数逻辑可根据需求调整（比如忽略大小写）

3. 全文索引：大数据量最优解 #

SQL Server 2008支持全文索引，专门针对文本搜索优化，能自动处理拼写变体、同义词等场景，性能远高于函数匹配。

实现步骤： #

• 创建全文目录和索引：

CREATE FULLTEXT CATALOG ft_Medicines_Catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON medicines(Name) 
KEY INDEX PK_Medicines_Id; -- 替换为你的药品表主键索引名

• 使用FREETEXT查询，自动匹配语义/拼写相近的结果：

DECLARE @userInput NVARCHAR(100) = 'lasixxx';
SELECT Name 
FROM medicines 
WHERE FREETEXT(Name, @userInput);

• 若需更精确控制，可使用CONTAINS结合FORMSOF：

SELECT Name 
FROM medicines 
WHERE CONTAINS(Name, 'FORMSOF(FREETEXT, "' + @userInput + '")');

优势： #

• 专为大数据量文本搜索设计，性能极佳
• 无需手动维护变体规则，内置拼写匹配逻辑
• 完全兼容SQL Server 2008

4. 标准化存储：长期性能最优方案 #

通过标准化药品名称并创建索引，将所有变体映射到标准名称，查询时先标准化用户输入再匹配。

实现步骤： #

• 创建名称标准化函数（去除重复字符、统一格式，结合SOUNDEX匹配标准名称）：

CREATE FUNCTION dbo.StandardizeDrugName
(
    @name NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
    -- 统一小写
    SET @name = LOWER(@name);
    -- 去除连续重复字符（如lasixxx→lasix）
    WHILE PATINDEX('%([a-z])\1%', @name) > 0
        SET @name = STUFF(@name, PATINDEX('%([a-z])\1%', @name)+1, 1, '');
    -- 通过SOUNDEX匹配获取标准药品名称
    SET @name = (SELECT TOP 1 Name FROM medicines WHERE SOUNDEX(Name) = SOUNDEX(@name) ORDER BY LEN(Name));
    RETURN @name;
END

• 添加标准化计算列并创建索引：

ALTER TABLE medicines ADD StandardizedName AS dbo.StandardizeDrugName(Name);
CREATE NONCLUSTERED INDEX IX_Medicines_StandardizedName ON medicines(StandardizedName);

• 查询时先标准化用户输入，再匹配标准列：

DECLARE @userInput NVARCHAR(100) = 'lisax';
DECLARE @standardizedInput NVARCHAR(100) = dbo.StandardizeDrugName(@userInput);
SELECT Name 
FROM medicines 
WHERE StandardizedName = @standardizedInput;

优势： #

• 查询性能最优，直接利用索引匹配
• 一次标准化后无需重复处理变体规则

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