有2个字符串。这些字符串可能有所不同。我需要返回不同的值“ Difference”和这些值的“ Position”。
上面的帖子显示了类似的内容,但是,我的字符串没有任何分隔符,因此我在应用该方法时遇到了麻烦。两个字符串始终为24个字符长。但是,差异会有所不同,所以我不能只比较String1的位置1和String2的位置1。
理想情况下,考虑到每个位置都有其含义,最好用更少的差异表示方差值。但是,让我仅显示出差异将非常有帮助。
这很丑,但是...
首先,给自己一份的副本NGrams8K
。然后,您可以执行以下操作:
DECLARE @String1 varchar(8000) = 'abcd10234619843ab13',
@String2 varchar(8000) = 'bbcd10234619843ac14';
WITH C AS(
SELECT @String1 AS String1,
@String2 AS String2,
S1.[position],
S1.token AS C1,
S2.token AS C2
FROM dbo.NGrams8k(@String1,1) S1
JOIN dbo.NGrams8k(@String2,1) S2 ON S1.[position] = S2.position)
SELECT (SELECT '' + C.C2
FROM C
WHERE C.C1 != C.C2
ORDER BY C.[position]
FOR XML PATH(''),TYPE).value('.','varchar(8000)') AS Difference,
(SELECT ISNULL(NULLIF(C.C2,C.C1),'-')
FROM C
ORDER BY C.[position]
FOR XML PATH(''),TYPE).value('.','varchar(8000)') AS Ideal,
STUFF((SELECT CONCAT(',',C.[position])
FROM C
WHERE C.C1 != C.C2
ORDER BY C.[position]
FOR XML PATH(''),TYPE).value('.','varchar(8000)'),1,1,'') AS Position;
如果您使用的是SQL Server的更新版本和受支持的版本,则实际上要简单得多,只需扫描一次所需的值即可:
DECLARE @String1 varchar(8000) = 'abcd10234619843ab13',
@String2 varchar(8000) = 'bbcd10234619843ac14';
WITH C AS(
SELECT @String1 AS String1,
@String2 AS String2,
S1.[position],
S1.token AS C1,
S2.token AS C2
FROM dbo.NGrams8k(@String1,1) S1
JOIN dbo.NGrams8k(@String2,1) S2 ON S1.[position] = S2.position)
SELECT STRING_AGG(NULLIF(C.C2,C.C1),'') WITHIN GROUP (ORDER BY C.position) AS Difference,
STRING_AGG(ISNULL(NULLIF(C.C2,C.C1),'-'),'') WITHIN GROUP (ORDER BY C.position) AS Ideal,
STRING_AGG(CASE C.C1 WHEN C.C2 THEN NULL ELSE C.[position] END,',') WITHIN GROUP (ORDER BY C.position) AS Position
FROM C
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句