在SQL Server中查找两个字符串之间的字符差异数量

Question

我正在寻找一些内置函数，它可以找出两个字符串相差的字符数。

例如：

CharDiff('SO0035F', 'SO005F') = 1

CharDiff('12345', '34512') = 0

- jannagy02

---

我相信在SQL Server中没有内置的函数可以实现这个功能，否则它会在这里列出：http://msdn.microsoft.com/en-US/us-en/library/ms181984.aspx。但是编写自己的函数应该不难，此外，例如对于`CharDiff('SO0035F', 'SO035F')`，您期望的结果是什么（0在两个字符串中都存在，但计数不同），这个函数是否也应该返回1？ - DrCopyPaste

是的，我看到它写得不够清楚，我认为我应该为此编写自己的函数。你问题的答案是，它不仅应检查另一个字符串中是否存在该字符，而且它出现的次数也很重要。 - dvjanm

当比较 'ab' 和 'cde' 时，您期望得到什么结果？ - msi77

我本来期望得到5，但现在清楚的是，如果不编写我的自己的函数，就无法解决这个问题。 - dvjanm

@jannagy02，由于您在我的回答之后发表了评论，请问您是否考虑了我的回答？ - DrCopyPaste

@DrCopyPaste 当然可以，但是如果你再读一遍我的问题，我强调了内置。但是我可能会在几天后回到这个问题，请耐心等待，如果它帮助了我，我会接受 :) - dvjanm

Answer 1

我需要一些略微不同的东西。我需要将“1234”与“1243”进行比较，即使它们包含相同的字符，也要显示为2个不同的字符。

我想出了以下方法：

 CREATE FUNCTION dbo.CharDiff (@string1 NVARCHAR(MAX), @string2 NVARCHAR(MAX))
 RETURNS INT
 AS
 BEGIN
     DECLARE @diffs INT = 0
     WHILE LEN(@string1) > 0 AND LEN(@string2) > 0
     BEGIN
        IF SUBSTRING(@string1,1,1) <> SUBSTRING(@string2,1,1)
            SELECT @diffs = @diffs + 1
        SELECT @string1 = SUBSTRING(@string1,2,9999)
        SELECT @string2 = SUBSTRING(@string2,2,9999)
     END
     RETURN @diffs + LEN(@string1) + LEN(@string2)
 END

- Scott R. Frost

Answer 2

作为 此页面 列出了 SQL Server 中所有可用的字符串函数，我相信没有内置的功能可以完全满足这种使用情况。

然而，通过参考 这篇帖子，我想到了以下代码，似乎可以满足您的需求：

CREATE FUNCTION dbo.CharDiff (@string1 NVARCHAR(MAX), @string2 NVARCHAR(MAX))
RETURNS INT
AS
BEGIN

    DECLARE @allDifferences INT = 0
    DECLARE @charCount1 INT
    DECLARE @charCount2 INT

    --do this as long as both strings are longer than 0
    WHILE LEN(@string1) > 0 AND LEN(@string2) > 0
    BEGIN
        --get char count for the character at index 1 in string 1
        SELECT @charCount1 = (LEN(@string1) - LEN(REPLACE(@string1, SUBSTRING(@string1, 1, 1), '')))
        --get char count for the character at index 1 in string 1 but for string2
        SELECT @charCount2 = (LEN(@string2) - LEN(REPLACE(@string2, SUBSTRING(@string1, 1, 1), '')))

        --strip all chars that now have been counted from string 2
        SELECT @string2 = REPLACE(@string2, SUBSTRING(@string1, 1, 1),'')
        --strip all chars that now have been counted from string 1
        SELECT @string1 = REPLACE(@string1, SUBSTRING(@string1, 1, 1),'')

        --add difference to counting variable
        SELECT @allDifferences = @allDifferences + ABS(@charCount1 - @charCount2)
    END

    --is there any rest length on any of those string?
    SELECT @allDifferences = @allDifferences + ABS(LEN(@string1) - LEN(@string2))


    RETURN @allDifferences
END

基本上，我只是在两个字符串中计算当前存在于索引1的字符的出现次数。 然后，从这两个字符串中删除所有已经计数过的字符（这样每次迭代时索引1就会持有另一个字符），只要这两个字符串中仍然存在任何字符。在循环之后可能仍然存在的字符串的剩余长度可以简单地添加到@allDifferences中。

- DrCopyPaste

Answer 3

根据Scott R. Frost的回答，此解决方案展示了将'1234'与'1243'进行比较，即使它们包含相同的字符，也会有2个不同的字符。

对于长度不超过8个字符的字符串：

SELECT
    LEN(REPLACE(CONVERT(varchar(8),
                        CONVERT(binary(8),
                                CONVERT(bigint, CONVERT(binary(8), String1)) ^ CONVERT(binary(8), String2)
                                )
                        ) COLLATE Latin1_General_BIN,
                char(0),
                '')) AS XORCharDiff
FROM (VALUES ('Example1', 'Example2')) AS X(String1, String2)

对于所有字符串长度：

CREATE FUNCTION dbo.XORCharDiff (@string1 VARCHAR(MAX), @string2 VARCHAR(MAX))
RETURNS INT
AS
BEGIN
    DECLARE @diffs INT = 0
    WHILE LEN(@string1) > 0 AND LEN(@string2) > 0
    BEGIN
        SELECT @diffs = @diffs +
                LEN(REPLACE(CONVERT(varchar(8),
                        CONVERT(binary(8),
                                CONVERT(bigint, CONVERT(binary(8), SUBSTRING(@string1, 1, 8))) ^ CONVERT(binary(8),  SUBSTRING(@string2, 1, 8))
                                )
                            ) COLLATE Latin1_General_BIN,
                    char(0),
                    ''))
        SELECT @string1 = SUBSTRING(@string1,9,8)
        SELECT @string2 = SUBSTRING(@string2,9,8)
    END
    RETURN @diffs + LEN(@string1) + LEN(@string2)
END

字符串比较基于两个字符串二进制表示的按位异或运算。如果在两个字符串中有匹配的字符，则这些字符将“相互抵消”，结果为0x00/char(0)。

示例： 01001000 01101001 'Hi'（字符串1） 01001000 01100001 'Ha'（字符串2） 00000000 00001000 按位异或结果

通过删除char(0)，可以减少字符串长度，即减去匹配字符的数量。

所有操作都可以在单行中完成，无需循环和函数，从而实现高速解决方案。

进一步解释如下： 需要进行Bigint转换，因为按位异或不能在两个二进制数上操作。因为Bigint有8字节，所以每次比较的限制为8个字符。 需要使用COLLATE Latin1_General_BIN，否则将移除不可打印字符。

请参见以下解决方案和CharDiff函数（对于100K个8个字符长的字符串）之间的时间比较。

XOR CharDiff (single line max 8 characters)
 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 109 ms.

(100000 rows affected)

XOR CharDiff function (all string lengths)
 SQL Server Execution Times:
   CPU time = 3188 ms,  elapsed time = 3979 ms.

(100000 rows affected)

CharDiff
 SQL Server Execution Times:
   CPU time = 7828 ms,  elapsed time = 10442 ms.

(100000 rows affected)

- EHuijzer

Answer 4

一个更基于集合的 Scott's answer 版本是

DECLARE @String1 VARCHAR(10) = '1234', 
        @String2 VARCHAR(10) = '1243'


SELECT COUNT(*)
FROM GENERATE_SERIES(1, GREATEST(LEN(@String1 + 'X'), LEN(@String2 + 'X')) - 1)
WHERE SUBSTRING(@String1, value, 1) <>  SUBSTRING(@String2, value, 1)

这需要 SQL Server 2022+。

使用 GENERATE_SERIES 将字符串拆分为字符的相同方法也可以作为不同定义的 CharDiff 的第一步。

- Martin Smith