我正在寻找一些内置函数,它可以找出两个字符串相差的字符数。
例如:
CharDiff('SO0035F', 'SO005F') = 1
CharDiff('12345', '34512') = 0
我正在寻找一些内置函数,它可以找出两个字符串相差的字符数。
例如:
CharDiff('SO0035F', 'SO005F') = 1
CharDiff('12345', '34512') = 0
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
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
中。
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
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)
一个更基于集合的 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
的第一步。