CODEWITHSUNDEEP
sql-server
Satscreate
Satscreate

Reputation: 535

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

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
)

Upvotes: 0

Views: 1275

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8819

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

Upvotes: 1

Related Questions