How to Compare Two Columns and Display the difference in a new Column - SQL Server

Question

I want to compare two columns of type nvarchar and show the output in a new column, is there a way to achieve this in Microsoft SQL Server?

Example:

COL1                                    COL2
--------------------                    -----------------------------------
This is my test case                    This is not my test Case, leave me alone


OUTPUT
---------------------
not, leave me alone

"Display the difference" is a very vague description of what you're trying to do. What if it would say "This is This is not my test..." for example. Please write a proper description, and also what you've tried so far. — James Z, Feb 04 '21 at 09:49
SQL Server doesn't have a datatype `CLOB`.... what **actual** datatype are you using here?? — marc_s, Feb 04 '21 at 09:54
Does this answer your question? [SQL - How do you compare a CLOB](https://stackoverflow.com/questions/110587/sql-how-do-you-compare-a-clob) — Amira Bedhiafi, Feb 04 '21 at 10:41
Oracle's `CLOB` type [appears](http://www.sqlines.com/oracle/datatypes/clob) to be equivalent to T-SQL's `NVARCHAR(MAX)` type. Besides that, I find the example confusing. Are you comparing words individually or are you "subtracting" `COL1` from `COL2` from left to right (and does the output have to be in the original order)? Are capitals important (`case` <> `Case`) and why is `my` in the expected output (`me` <> `my`)? — Sander, Feb 04 '21 at 11:03
@sander, sorry there was a typo in my expected output, I've corrected it. I want to compare words between two column and display the difference in new column — Naga, Feb 04 '21 at 12:33
@marc_s you are right, yes MS SQL doesn't have CLOB, I have DBs in both oracle and MS SQL, I want to achieve this in MS SQL wrongly I referred to column type in oracle, correct column type nvarchar — Naga, Feb 04 '21 at 12:36
@JamesZ all what I've tried and searched so far in internet is not even close to what I was expecting, I tried using DIFFERENCE function, however adding that would not help this discussion. So I didn't add that to my Qs. — Naga, Feb 04 '21 at 12:39
One solution would be to split `col1` and `col2` values into separate words with `string_split()` but this would produce `case` and `Case,` which will not be excluded as the same word. And yet the `,` has to be part of the result... The alternative would be to parse both `col1` and `col2` values from left to right and remove any common characters in the order of their occurrence, `case` + `Case,` would then produce `,` but `abc def`+ `abcz def` will produce `z`... Do you really need that `,` in the result?? — Sander, Feb 04 '21 at 12:41
@sander - comma (,) can be removed as common tokens at this stage. — Naga, Feb 04 '21 at 13:02
(1) I don't fully follow the rules that are used for the transformation. (2) SQL is probably not the right tool for such string manipulation. — Gordon Linoff, Feb 04 '21 at 13:23

Sander · Accepted Answer · 2021-02-04T16:32:31.797

The solution below works one way: col1 words are removed from col2.

Sample data

create table test
(
  id int,
  col1 nvarchar(max),
  col2 nvarchar(max)
);

insert into test (id, col1, col2) values
(1, 'This is my test case', 'This is not my test Case, leave me alone');

Solution

with cte as
(
  select t.id,
         replace(s.value, ',', '') as word
  from test t
  cross apply string_split(t.col2, ' ') s
    except
  select t.id,
         replace(s.value, ',', '')
  from test t
  cross apply string_split(t.col1, ' ') s
)
select string_agg(c.word, ' ') as result
from cte c
group by c.id;

Result

result
------------------
alone leave me not

Fiddle to see things in action with intermediate results.

New solution

Perhaps this version does not look so clean, but it should preserve the word order...

with cte as
(
  select t.id,
         row_number() over(order by (select null)) as sort,
         replace(s.value, ',', '') as word
  from test t
  cross apply string_split(t.col2, ' ') s
  where not exists ( select 'x'
                     from test t
                     cross apply string_split(t.col1, ' ') s2
                     where replace(s2.value, ',', '') = replace(s.value, ',', '') )
)
select string_agg(c.word, ' ') within group (order by c.sort) as result
from cte c
group by c.id;

New result

result
------------------
not leave me alone

New fiddle.

Thanks Sander, is it possible to retrieve the result in the same order as it appears in COL2? like "not leave me alone" — Naga, Feb 04 '21 at 13:48
Answer updated with a new version that maintains the word order. — Sander, Feb 04 '21 at 16:33

How to Compare Two Columns and Display the difference in a new Column - SQL Server

1 Answers1