如何编写SQL WHERE语句匹配仅差一个字符的等长字符串？

要匹配长度相同且最多一个字符不同的字符串，核心思路有两种：一是逐个字符对比统计差异数，二是利用编辑距离（Levenshtein距离）筛选。以下是具体实现方案：

方案一：通用字符对比法（无依赖，兼容所有SQL数据库） #

如果你的字符串长度固定，直接逐个位置对比字符，统计不同的数量≤1即可。以长度为6的字符串为例：

WHERE 
    -- 先确保两个字符串长度一致
    LENGTH(str1) = LENGTH(str2)
    AND (
        -- 完全相同
        (SUBSTRING(str1,1,1) = SUBSTRING(str2,1,1) 
         AND SUBSTRING(str1,2,1) = SUBSTRING(str2,2,1) 
         AND SUBSTRING(str1,3,1) = SUBSTRING(str2,3,1) 
         AND SUBSTRING(str1,4,1) = SUBSTRING(str2,4,1) 
         AND SUBSTRING(str1,5,1) = SUBSTRING(str2,5,1) 
         AND SUBSTRING(str1,6,1) = SUBSTRING(str2,6,1))
        -- 第1位不同，其余相同
        OR (SUBSTRING(str1,1,1) != SUBSTRING(str2,1,1) 
            AND SUBSTRING(str1,2,1) = SUBSTRING(str2,2,1) 
            AND SUBSTRING(str1,3,1) = SUBSTRING(str2,3,1) 
            AND SUBSTRING(str1,4,1) = SUBSTRING(str2,4,1) 
            AND SUBSTRING(str1,5,1) = SUBSTRING(str2,5,1) 
            AND SUBSTRING(str1,6,1) = SUBSTRING(str2,6,1))
        -- 第2位不同，其余相同
        OR (SUBSTRING(str1,1,1) = SUBSTRING(str2,1,1) 
            AND SUBSTRING(str1,2,1) != SUBSTRING(str2,2,1) 
            AND SUBSTRING(str1,3,1) = SUBSTRING(str2,3,1) 
            AND SUBSTRING(str1,4,1) = SUBSTRING(str2,4,1) 
            AND SUBSTRING(str1,5,1) = SUBSTRING(str2,5,1) 
            AND SUBSTRING(str1,6,1) = SUBSTRING(str2,6,1))
        -- 第3位不同，其余相同
        OR (SUBSTRING(str1,1,1) = SUBSTRING(str2,1,1) 
            AND SUBSTRING(str1,2,1) = SUBSTRING(str2,2,1) 
            AND SUBSTRING(str1,3,1) != SUBSTRING(str2,3,1) 
            AND SUBSTRING(str1,4,1) = SUBSTRING(str2,4,1) 
            AND SUBSTRING(str1,5,1) = SUBSTRING(str2,5,1) 
            AND SUBSTRING(str1,6,1) = SUBSTRING(str2,6,1))
        -- 第4位不同，其余相同
        OR (SUBSTRING(str1,1,1) = SUBSTRING(str2,1,1) 
            AND SUBSTRING(str1,2,1) = SUBSTRING(str2,2,1) 
            AND SUBSTRING(str1,3,1) = SUBSTRING(str2,3,1) 
            AND SUBSTRING(str1,4,1) != SUBSTRING(str2,4,1) 
            AND SUBSTRING(str1,5,1) = SUBSTRING(str2,5,1) 
            AND SUBSTRING(str1,6,1) = SUBSTRING(str2,6,1))
        -- 第5位不同，其余相同
        OR (SUBSTRING(str1,1,1) = SUBSTRING(str2,1,1) 
            AND SUBSTRING(str1,2,1) = SUBSTRING(str2,2,1) 
            AND SUBSTRING(str1,3,1) = SUBSTRING(str2,3,1) 
            AND SUBSTRING(str1,4,1) = SUBSTRING(str2,4,1) 
            AND SUBSTRING(str1,5,1) != SUBSTRING(str2,5,1) 
            AND SUBSTRING(str1,6,1) = SUBSTRING(str2,6,1))
        -- 第6位不同，其余相同
        OR (SUBSTRING(str1,1,1) = SUBSTRING(str2,1,1) 
            AND SUBSTRING(str1,2,1) = SUBSTRING(str2,2,1) 
            AND SUBSTRING(str1,3,1) = SUBSTRING(str2,3,1) 
            AND SUBSTRING(str1,4,1) = SUBSTRING(str2,4,1) 
            AND SUBSTRING(str1,5,1) = SUBSTRING(str2,5,1) 
            AND SUBSTRING(str1,6,1) != SUBSTRING(str2,6,1))
    )

优缺点：兼容性拉满，不需要任何扩展或自定义函数；但仅适合固定长度的字符串，长度变化时需要修改代码。

方案二：编辑距离法（灵活适配任意长度） #

Levenshtein距离（编辑距离）用于计算两个字符串之间最少需要多少次单字符编辑（替换、插入、删除）才能互相转换。因为我们要求长度相同，所以插入/删除操作不可能，只需筛选距离≤1的记录（0=完全相同，1=单个字符替换）。

PostgreSQL #

先确保安装fuzzystrmatch扩展（大部分环境默认已安装），然后直接使用：

WHERE 
    LENGTH(str1) = LENGTH(str2)
    AND levenshtein(str1, str2) <= 1

MariaDB/MySQL #

• MariaDB 10.0.5+：内置LEVENSHTEIN函数，直接用：

WHERE 
    CHAR_LENGTH(str1) = CHAR_LENGTH(str2)
    AND LEVENSHTEIN(str1, str2) <= 1

• MySQL：需要手动创建自定义函数，执行以下SQL定义函数后，即可使用上面的语句：

DELIMITER //
CREATE FUNCTION LEVENSHTEIN(str1 VARCHAR(255), str2 VARCHAR(255)) 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE len1, len2, i, j, cost, min_dist INT;
    DECLARE s1_char CHAR;
    DECLARE dist_table INT(1000);
    
    SET len1 = CHAR_LENGTH(str1);
    SET len2 = CHAR_LENGTH(str2);
    
    IF len1 = 0 THEN RETURN len2; END IF;
    IF len2 = 0 THEN RETURN len1; END IF;
    
    SET dist_table = 0;
    
    FOR i FROM 0 TO len1 DO
        SET dist_table = dist_table + (i << 16);
    END FOR;
    FOR j FROM 0 TO len2 DO
        SET dist_table = dist_table + j;
    END FOR;
    
    FOR i FROM 1 TO len1 DO
        SET s1_char = SUBSTRING(str1, i, 1);
        FOR j FROM 1 TO len2 DO
            SET cost = IF(SUBSTRING(str2, j, 1) = s1_char, 0, 1);
            
            SET min_dist = LEAST(
                (dist_table >> ((j-1) << 1)) & 0xFFFF, 
                ((dist_table >> (j << 1)) & 0xFFFF) + 1, 
                ((dist_table >> ((j-1) << 1)) & 0xFFFF) + cost
            );
            
            SET dist_table = dist_table & ~(0xFFFF << (j << 1));
            SET dist_table = dist_table | (min_dist << (j << 1));
        END FOR;
    END FOR;
    
    RETURN (dist_table >> (len2 << 1)) & 0xFFFF;
END //
DELIMITER ;

SQL Server #

SQL Server无内置编辑距离函数，需自定义：

CREATE FUNCTION dbo.LevenshteinDistance
(
    @str1 NVARCHAR(4000),
    @str2 NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
    DECLARE @len1 INT = LEN(@str1), @len2 INT = LEN(@str2);
    DECLARE @distance TABLE (i INT, j INT, val INT);
    
    IF @len1 = 0 RETURN @len2;
    IF @len2 = 0 RETURN @len1;
    
    INSERT INTO @distance (i, j, val)
    SELECT 0, j, j FROM (SELECT TOP (@len2+1) j = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM sys.all_columns) AS t
    UNION ALL
    SELECT i, 0, i FROM (SELECT TOP (@len1+1) i = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM sys.all_columns) AS t;
    
    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(@str1, @i, 1) = SUBSTRING(@str2, @j, 1) THEN 0 ELSE 1 END;
            
            UPDATE @distance
            SET val = (
                SELECT MIN(val)
                FROM (
                    SELECT val + 1 FROM @distance WHERE i = @i-1 AND j = @j
                    UNION ALL
                    SELECT val + 1 FROM @distance WHERE i = @i AND j = @j-1
                    UNION ALL
                    SELECT val + @cost FROM @distance WHERE i = @i-1 AND j = @j-1
                ) AS t
            )
            WHERE i = @i AND j = @j;
            
            SET @j = @j + 1;
        END
        SET @i = @i + 1;
    END
    
    DECLARE @result INT;
    SELECT @result = val FROM @distance WHERE i = @len1 AND j = @len2;
    RETURN @result;
END;

定义后使用：

WHERE 
    LEN(str1) = LEN(str2)
    AND dbo.LevenshteinDistance(str1, str2) <= 1

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