How to find diff between two strings in SQL

Question

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

for example,

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

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

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

Answer 1

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...

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

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

Try this:

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,'')

Answer 2

First take the function from the following link Parse comma-separated string to make IN List of strings in the Where clause

Then use the following query;

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

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

SELECT
    s1.val
    ,s2.val
FROM [dbo].[f_split](@String1,',') s1
FULL OUTER JOIN [dbo].[f_split](@String2,',') s2
    ON s1.val = s2.val
WHERE s1.val IS NULL
    OR s2.val IS NULL

Which will give you the following results;

val val
a   NULL
c   NULL
d   NULL