如何在SQL中找到两个字符串之间的差异 [英] How to find diff between two string in SQL

问题描述

我有两个字符串，我想在 SQL 中获取两个字符串内容之间的差异??

I have two strings, I want to get difference between contents of two strings in SQL ??

例如

Declare @String1 as varchar(100)='a,b,c,d,e';

Declare @String2 as varchar(100)='b,e';

现在我想要两个字符串之间的差异为a，c，d"

Now i want difference between two strings as "a,c,d"

推荐答案

两个字符串都必须拆分成各自的部分.在 SQL-Server 2008 中，这最好使用 XML 方法来完成.

Both strings must be split into their parts. In SQL-Server 2008 this is best to be done with an XML approach.

注意:如果您的数据可能包含诸如 <>öä@€& 之类的禁止字符，而不仅仅是像您的示例中那样的纯拉丁字符，您应该需要一些额外的努力...

attention: If your data might include forbidden characters like <>öä@€& and not just plain latin characters like in your example, you'd need some extra effort...

剩下的就相当简单了:只需取出 @String1 中没有在 @String2 中找到的所有部分.

The rest is fairly easy: Just take all parts of @String1 which are not found in @String2.

连接的结果 - 再次 - 最好通过 XML 完成

The concatenated result is - again - best to be done via XML

试试这个:

Declare @String1 as varchar(100)='a,b,c,d,e';

Declare @String2 as varchar(100)='b,e';

WITH FirstStringSplit(S1) AS
(
    SELECT CAST('<x>' + REPLACE(@String1,',','</x><x>') + '</x>' AS XML)
)
,SecondStringSplit(S2) AS
(
    SELECT CAST('<x>' + REPLACE(@String2,',','</x><x>') + '</x>' AS XML)
)

SELECT STUFF(
(
    SELECT ',' + part1.value('.','nvarchar(max)')
    FROM FirstStringSplit
    CROSS APPLY S1.nodes('/x') AS A(part1)
    WHERE part1.value('.','nvarchar(max)') NOT IN(SELECT B.part2.value('.','nvarchar(max)')
                                                  FROM SecondStringSplit 
                                                  CROSS APPLY S2.nodes('/x') AS B(part2)
                                                  ) 
    FOR XML PATH('')

),1,1,'')

这篇关于如何在SQL中找到两个字符串之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！