How to compare two comma-separated string lists using SQL Server

Question

i have data in two tables and same type columns with comma separated as below,

TableName       : ColumnName : Values
------------------------------------------------
TargetTable1    : Column1    : 'Class1,Class2,Class3'
SourceTable2    : Column1    : 'Class4,Class5,Class1'

Here TargetTable1 is where i am going to update the column contents by comparing against SourceTable2 (Temporary).

Expected Result:

TableName       : ColumnName : Values
------------------------------------------------
TargetTable1    : Column1    : 'Class1,Class2,Class3,Class4,Class5'

Any idea how to do using UPDATE and CASE statements as like below, BTW below query does append by checking like but how do i transform below query to compare against two comma separated values?

Update SET TargetTable1.Column1 = (
    SELECT
        CASE
          WHEN SourceTable2.Column1 IS NULL
            THEN TargetTable1.Column1 
            ELSE
                 (
                   SELECT
                     CASE
                       WHEN TargetTable1.Column1 LIKE '%' + SourceTable2.Column1 + '%'
                         THEN TargetTable1.Column1 
                         ELSE TargetTable1.Column1 +','+ SourceTable2.Column1
                     END
            )
        END
)

At the moment we designed to handle this in RDBMS layer (i mean may not be able to change design), so might looking for a good solutions though !!!!! — Satscreate, Apr 16 '21 at 11:25
So what's the big deal. Step one is to split the strings into rows. You then recombine the distinct values from all those rows. If order is important, then you need to factor that into your logic - the tsql string_split function does not support that aspect. Better yet - fix your design! Lack of proper normalization will require the repeated use of bulky, inefficient, and error-prone code. — SMor, Apr 16 '21 at 11:40
Just wrote an answer to almost exactly the same question https://stackoverflow.com/questions/67122223/compare-if-two-strings-contain-the-same-words/67122985#67122985 — Charlieface, Apr 16 '21 at 12:24

score 1 · Answer 1 · answered Apr 16 '21 at 11:51

Avoid creating designs that store delimited data. But since you're stuck with this already try something like the following...

update TT
set Column1 = (
  select string_agg(splitsville.value, N',')
  from (
    select value
    from string_split(TT.Column1, N',')
    union
    select value
    from dbo.SourceTable1 ST
    cross apply string_split(ST.Column1, N',')
    where ST.SomeID = TT.SomeID --<<-- Some specific ID common to both tables
  ) splitsville
)
from dbo.TargetTable1 TT
where TT.SomeID = 47; --<<-- Some specific ID common to both tables

How to compare two comma-separated string lists using SQL Server

1 Answers1