Nilesh Bankar Nilesh Bankar - 24 days ago 8
SQL Question

How to get exact character diff between two string in sql

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

for example,

Declare @String1 as varchar(100)='Please check requirement and let me know your feedback.';

Declare @String2 as varchar(100)='Please checkout requirement & let me know your feedback';


Result should be like the words which are not common in both the string.

e.g checkout , &

Answer

This code will first split the strings into single words and then check for words which do not exist in the other string

Declare @String1 as varchar(100)='Please check requirement and let me know your feedback.';
Declare @String2 as varchar(100)='Please checkout requirement & let me know your feedback';

WITH s1_Splitted AS
(
    SELECT wrd.value('.','nvarchar(max)') AS Word
    FROM
    (SELECT (
            SELECT CAST('<x>' + REPLACE((SELECT @String1 AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)
            ) AS Casted
    ) AS derived
    CROSS APPLY derived.Casted.nodes('/x') AS A(wrd)
)
,s2_Splitted AS
(
    SELECT wrd.value('.','nvarchar(max)') AS Word
    FROM
    (SELECT (
            SELECT CAST('<x>' + REPLACE((SELECT @String2 AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)
            ) AS Casted
    ) AS derived
    CROSS APPLY derived.Casted.nodes('/x') AS A(wrd)
)
SELECT 'only in s1',s1.Word
FROM s1_Splitted AS s1
WHERE NOT EXISTS(SELECT 1 FROM s2_Splitted AS s2 WHERE s2.Word=s1.Word)
UNION ALL
SELECT 'only in s2',s2.Word
FROM s2_Splitted AS s2
WHERE NOT EXISTS(SELECT 1 FROM s1_Splitted AS s1 WHERE s1.Word=s2.Word)

The result (attention: the . after feedback is taken as two different words...)

only in s1  and
only in s1  check
only in s1  feedback.
only in s2  &
only in s2  checkout
only in s2  feedback
Comments