代码之家 › 专栏 › 技术社区 › MAK

获取匹配字符串的百分比

sql-server-2008-r2 tsql sql-server

MAK · 技术社区 · 7 年前

我有两个字符串要匹配,并得到匹配的百分比。

鉴于:

String 1: John Smith Makde

String 2: Makde John Smith

使用了以下用户定义的标量函数。

CREATE FUNCTION [dbo].[udf_GetPercentageOfTwoStringMatching]
(
    @string1 NVARCHAR(1000)
    ,@string2 NVARCHAR(1000)
)
RETURNS INT

--WITH ENCRYPTION 
AS
BEGIN

    DECLARE @levenShteinNumber INT

    DECLARE @string1Length INT = LEN(@string1), @string2Length INT = LEN(@string2)
    DECLARE @maxLengthNumber INT = CASE WHEN @string1Length > @string2Length THEN @string1Length ELSE @string2Length END

    SELECT @levenShteinNumber = [dbo].[f_ALGORITHM_LEVENSHTEIN] (@string1  ,@string2)

    DECLARE @percentageOfBadCharacters INT = @levenShteinNumber * 100 / @maxLengthNumber

    DECLARE @percentageOfGoodCharacters INT = 100 - @percentageOfBadCharacters

    -- Return the result of the function
    RETURN @percentageOfGoodCharacters

END

函数调用:

SELECT dbo.f_GetPercentageOfTwoStringMatching('John Smith Makde','Makde John Smith')

输出:

但当我给两个字符串相同的位置时:

SELECT dbo.f_GetPercentageOfTwoStringMatching('John Smith Makde','John Smith Makde')

输出:

预期结果 :由于两个字符串单词相同,但顺序不同,我希望100%匹配百分比。

0 回复 | 直到 7 年前

Alan Burstein 7 年前

+1.回答问题。你似乎在试图确定这两个名字有多相似。很难确定你是如何做到这一点的。例如,我非常熟悉Levenshtein距离,但不明白您是如何使用它的。我可以用两种方法让你开始。这并不是一个完整的答案,而是你尝试做任何事情所需要的工具。

要比较匹配的“名称部分”的数量,可以使用 DelimitedSplit8K 这样地:

DECLARE 
  @String1 VARCHAR(100) = 'John Smith Makde Sr.',
  @String2 VARCHAR(100) = 'Makde John Smith Jr.';

SELECT COUNT(*)/(1.*LEN(@String1)-LEN(REPLACE(@string1,' ',''))+1)
FROM
(
  SELECT      s1.item
  FROM        dbo.delimitedSplit8K(@String1,' ') AS s1
  INTERSECT
  SELECT      s2.item
  FROM        dbo.delimitedSplit8K(@String2,' ') AS s2
) AS a

在这里,我将这些名称拆分为原子值,并计算哪些匹配。然后我们把这个数除以值的个数。3/4=0.75,占75%;四个名字中有三个匹配。

NGrams8K 就像这样:

DECLARE 
  @String1 VARCHAR(100) = 'John Smith Makde Sr.',
  @String2 VARCHAR(100) = 'Makde John Smith Jr.';

SELECT (1.*f.L-f.MM)/f.L
FROM
(
  SELECT 
    MM = SUM(ABS(s1.C-s2.C)), 
    L  = CASE WHEN LEN(@String1)>LEN(@string2) THEN LEN(@String1) ELSE LEN(@string2) END
  FROM
  (
    SELECT s1.token, COUNT(*)
    FROM   samd.NGrams8k(@String1,1) AS s1
    GROUP BY s1.token
  ) AS s1(T,C)
  JOIN 
  (
    SELECT s1.token, COUNT(*)
    FROM   samd.NGrams8k(@String2,1) AS s1
    GROUP BY s1.token
  ) AS s2(T,C)
  ON  s1.T=s2.T  -- Letters that are equal
  AND s1.C<>s2.C -- ... but the QTY is different
) AS f;

在这里,我们计算字符并减去不匹配的字符。有两个(一个额外的J和一个额外的S)。两个字符串的长度为20,字母和数量相等时有18个字符。18/20=0.9或90%。

再说一次,你正在做的事情并不复杂,我只需要更详细的信息就可以得到更好的答案。

Gottfried Lesigang 7 年前

一次又一次地这样做数百万行将是一场噩梦。。。我会添加另一列(或 1:1 相关侧桌)永久存储 正常化 一串试试这个:

--创建一个模型表,并用一些虚拟数据填充它

CREATE TABLE #MockUpYourTable(ID INT IDENTITY, SomeName VARCHAR(1000));
INSERT INTO #MockUpYourTable VALUES('Makde John Smith')
                                  ,('Smith John Makde')
                                  ,('Some other string')
                                  ,('string with with duplicates with');
GO

--添加一列以存储规范化的字符串

ALTER TABLE #MockupYourTable ADD NormalizedName VARCHAR(1000);
GO

--使用此脚本将字符串拆分为片段,并将其重新连接为规范有序的、重复的自由字符串。

UPDATE #MockUpYourTable SET NormalizedName=CAST('<x>' + REPLACE((SELECT LOWER(SomeName) AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)
                                            .query(N'
                                                    for $fragment in distinct-values(/x/text())
                                                    order by $fragment
                                                    return $fragment
                                                    ').value('.','nvarchar(1000)');           
GO

--检查结果

SELECT * FROM #MockUpYourTable

ID  SomeName                            NormalizedName
----------------------------------------------------------
1   Makde John Smith                    john makde smith
2   Smith John Makde                    john makde smith
3   Some other string                   other some string
4   string with with duplicates with    duplicates string with

--Clean-Up
GO
DROP TABLE #MockUpYourTable

暗示使用触发器 ON INSERT, UPDATE 使这些值保持同步。

现在,您可以对希望与之进行比较的字符串使用相同的转换,并使用以前的方法。由于重新排序,相同的片段将返回100%的相似性。