How to replace a value in comma separated string column in SQL Server

Question

I have a comma-separated value like 1,2,3,4 in one the column in a table in my SQL Server database. I want to replace a particular value in the comma separated string. i.e., from 1,2,3 I have to replace 1 with 5 and 2 with 6. The expected output is 5,6,3.

I will have the value 1 and 2 in multiple rows. So I need to update it in all the rows. I have a table which contains the new value to be updated (i.e., 5 and 6).

In short, I have a table having comma separated values in one of the columns and I have another table which contains the new value. I need to update the comma separated value with the new value.

Answer 1

WITH cte  AS (SELECT A.*,
  T.pkcolumn, 
                T.column1 
         FROM   table1 AS T 
                CROSS apply String_split(column1, ',') AS A 
         WHERE  column1 = '<oldValue>' 
                 OR column1 LIKE '<oldValue>,%' 
                 OR column1 LIKE '%,<oldValue>,%' 
                 OR column1 LIKE '%,<oldValue>') 
UPDATE Y 
SET    column1 = Stuff((SELECT ',' + CASE WHEN value = '<oldValue>' THEN 
                               '<newValue>' 
                                      ELSE value 
                                                END 
                        FROM   cte t1 
                        WHERE  t1.pkcolumn = t2.pkcolumn 
                        FOR xml path ('')), 1, 1, '') 
FROM   cte t2 
       LEFT OUTER JOIN table1 AS Y 
                    ON Y.pkcolumn = t2.pkcolumn `