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.

The best solution is to not do this (I know: not a helpful comment, there may be "*reasons*"). If the data in that column were in its own table then all the power of a RDBMS for processing rows would be at your disposal. Instead your stuck with the very limited test processing capabilities of an RDBMS. — Richard, Sep 24 '18 at 07:38
While you are moving the data into a new table, why don't just `SELECT REPLACE` instead of updating the table? — Ilyes, Sep 24 '18 at 07:46
@Squirrel `48,49,51,55,57` this is the old data and it should be replaced with `100,101,102,103,104`. This new data is there in another table with olddata against its new data. Like 48 100 49 101 51 102 55 103 57 104 The comma seperated column is of nvarchar type — Aswin Sathyan, Sep 24 '18 at 07:54

Taffy · Accepted Answer · 2018-09-25T04:53:32.677

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 `

score 0 · Answer 2 · answered Sep 24 '18 at 08:06

you can move your comma seperated values from your row into a new table with a fetch and a function like this;

CREATE FUNCTION [dbo].[SplitToItems]
(
@pString    NVARCHAR(3999), --!! DO NOT USE MAX DATA-TYPES
@pDelimiter CHAR(1)
)
RETURNS @Items TABLE
(
ItemNumber Integer,
Item nvarChar(100)
)
BEGIN
if Replace(@pString,'''','') = ''
set @pString=''
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                          --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
INSERT INTO @Items
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item       = SUBSTRING(SUBSTRING(@pString, l.N1, l.L1),1,100)
FROM cteLen l
RETURN
END

then you can update however you like to update them.

i d keep that table and drop the column.. but you may update them back to your table if its infrastructural or its too late to change.

score 0 · Answer 3 · edited Sep 24 '18 at 09:33

0

declare @str varchar(20) = '1,2,3,4,5,6'

select REPLACE(replace(@str, '1', '5'), '2', '6')
--replace 1 with 5 and 2 with 6

edited Sep 24 '18 at 09:33

marc_s

732,580
175
1,330
1,459

answered Sep 24 '18 at 09:18

Nitika

437
2
10

What happend if i have `1,2,3,4,5,6,11,12,13`?? All the 1 will get replaced by 2, If i execute this query `select REPLACE(replace(@str, '1', '5'), '2', '6')`. The output will be '2,2,3,4,6,6,22,22,23' – Aswin Sathyan Sep 24 '18 at 12:45

score -1 · Answer 4 · edited Sep 24 '18 at 09:33

-1

You can use query like

select REPLACE(REPLACE([Value], 1, 5), 2, 6) 
from TestTable

and insert into newer table column.

But still it is depends on your requirement.

Thanks.

edited Sep 24 '18 at 09:33

marc_s

732,580
175
1,330
1,459

answered Sep 24 '18 at 09:03

Nirav Patel

63
8

What happend if i have `1,2,3,4,5,6,11,12,13`?? All the 1 will get replaced by 2, If i execute this query `select REPLACE(replace(@str, '1', '5'), '2', '6')`. The output will be '2,2,3,4,6,6,22,22,23' – Aswin Sathyan Sep 24 '18 at 12:45

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

4 Answers4