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"

I agree, you're going to want to split these strings out into their components and then do something like a full outer join — Rich Benner
– Rich Benner, Commented Oct 27, 2016 at 9:27
Is the answer you expect only related to the values between commas? Because if you strip out of String1, values in String2, you'd get 'a,c,d,' (so with the last comma) — Jakub Szumiato
– Jakub Szumiato, Commented Oct 27, 2016 at 9:28

Gottfried Lesigang · Accepted Answer · 2016-10-27 09:33:23Z

9

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

edited Oct 27, 2016 at 9:33

answered Oct 27, 2016 at 9:30

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ahmed abdelqader Over a year ago

The Next error is raised: [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.(42000,1934)

Gottfried Lesigang Over a year ago

@ahmedabdelqader, what's the problem here? Did you try to set this ON? You might read this. Especially with older databases which were upgrade with scripts over a long periode, you might have problems with the default settings of some options which where nevere upgraded to the modern values...

ahmed abdelqader Over a year ago

You are right, after setting QUOTED_IDENTIFIER equals ON, everything is okay. thanks bro. voted it up :)

thecoolmacdude Over a year ago

I created a function using this answer and it worked great!

Gottfried Lesigang Over a year ago

@thecoolmacdude, these days you'd be better off with string_split() or a json based splitter.

Community · Accepted Answer · 2017-05-23 11:48:19Z

1

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

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 27, 2016 at 9:33

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

1 Comment

Kevin Pope Over a year ago

I ended up using the built-in STRING_SPLIT function here, which I appreciate may not have been available at the original time of this answer. Thanks for suggesting this strategy!

Jaydip Jadhav · Accepted Answer · 2016-10-27 09:44:02Z

1

Interesting task, Is it business requirement or what else?

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

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

;WITH StringOne AS (

     SELECT CAST('' AS VARCHAR(1)) AS ch, 1 as cnt
     UNION ALL
     SELECT CAST(SUBSTRING(@String1,cnt,1) AS VARCHAR(1)) AS ch, cnt+1 as cnt
     FROM StringOne 
     WHERE cnt <= LEN(@String1)
),StringTwo AS (

 SELECT CAST('' AS VARCHAR(1)) AS ch, 1 as cnt
 UNION ALL
 SELECT CAST(SUBSTRING(@String2,cnt,1) AS VARCHAR(1)) AS ch, cnt+1 as cnt
 FROM StringTwo 
 WHERE cnt <= LEN(@String2)
),ExceptOperation AS(

  SELECT ch FROM StringOne
  EXCEPT
  SELECT ch FROM StringTwo
)

SELECT STUFF((SELECT ','+ ch FROM ExceptOperation FOR XML PATH('')),1,1,'')

answered Oct 27, 2016 at 9:44

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

I like straight approaches. But - to be honest - I'd assume, that a,b,c is just an example and one cannot work this down on single letter count... :-) Anyway +1 from my side

Sanjiv Rajput · Accepted Answer · 2019-07-19 08:12:46Z

1

Simple Way

declare @string1 varchar(max),@string2 varchar(max)
set @string1='Apple, Orange, Banana'
set @string2='Apple, Orange, Banana, Pinapple, Grapes'

select REPLACE(@String2,@string1,'')

Result

, Pinapple, Grapes

answered Jul 19, 2019 at 8:12

Sanjiv Rajput

1452 silver badges13 bronze badges

Comments

djangojazz · Accepted Answer · 2022-09-09 21:24:39Z

Probably overkill but I noticed this would only go one direction and not the other. EG: Variable 2 has adds but ignores if variable 1 does. I would leverage faking a JSON array using new abilities in SQL 2016 and up utilizing OPENJSON. You can make this into a reusable function quite easily:

DECLARE
    @X VARCHAR(1024) = 'a,b,c'
,   @Y VARCHAR(1024) = 'a,b,d,e'
;

DECLARE 
    @Delimeter VARCHAR(8) = ','  --May use a comma, maybe a ;
,   @AddText VARCHAR(MAX) = 'Added: '  --Choose what to say for adds
,   @RemoveText VARCHAR(MAX) = 'Removed: '  --Choose what to say for removes

--Leave below variables alone as they are used for business logic
,   @ReturnText VARCHAR(MAX) = ''  
,   @Added VARCHAR(MAX)
,   @Removed VARCHAR(MAX)
;

SELECT @X = '["' + REPLACE(@X, '' + @Delimeter + '', '","') + '"]'  --get input ready for JSON 
SELECT @Y = '["' + REPLACE(@Y, '' + @Delimeter + '', '","') + '"]'  --get input ready for JSON 
;

SELECT @Added = STUFF(
    (SELECT ',' + v
    from 
        ( SELECT RTRIM(LTRIM(Value)) AS v FROM OPENJSON(@Y) EXCEPT  SELECT RTRIM(LTRIM(Value)) FROM OPENJSON(@X)) AS x
    FOR XML PATH('')), 1, 1, '')

SELECT @Removed = STUFF(
    (SELECT ',' + v
    from 
        ( SELECT RTRIM(LTRIM(Value)) AS v FROM OPENJSON(@X) EXCEPT  SELECT RTRIM(LTRIM(Value)) FROM OPENJSON(@Y)) AS x
    FOR XML PATH('')), 1, 1, '')

IF LEN(@Added) > 0
BEGIN
    IF LEN(@AddText) > 0
    BEGIN
        SELECT @ReturnText += @AddText
    END

    SELECT @ReturnText += @Added
END

IF LEN(@Removed) > 0
BEGIN
    IF LEN(@ReturnText) > 0
    BEGIN
        SELECT @ReturnText += ' '
    END

    IF LEN(@RemoveText) > 0
    BEGIN
        SELECT @ReturnText += @RemoveText
    END

    SELECT @ReturnText += @Removed
END

SELECT @ReturnText

This will yield:

Collectives™ on Stack Overflow

How to find diff between two strings in SQL

5 Answers 5

5 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related