CODEWITHSUNDEEP
sql-servert-sql
Andrew White
Andrew White

Reputation: 1789

Replacing a value in a comma separated list stored in a column

This would be 1 line of code in a normal programming language, but seems overly complex in SQL Server.

Given a table (yes no need to reply that comma separated values shouldn't be kept in columns):

| ID |  List        |
| 1  |  AB,ABC,ABCD |

What's the best way of replacing for example ONLY "AB" with "XX" and storing in back in the table?

Upvotes: 0

Views: 690

Answers (2)

dbajtr
dbajtr

Reputation: 2044

Bit hacky and a mess and assuming your on a version that can use string_split, this will work:

DECLARE @TABLE TABLE (ID INT, List VARCHAR(100))
INSERT INTO @TABLE VALUES
(1, 'AB,ABC,ABCD')


SELECT  ID
       ,STUFF((SELECT ', ' + CAST(IIF(value = 'AB', 'XX', value) AS VARCHAR(10)) [text()]
         FROM @TABLE 
         CROSS APPLY string_split (List,',')
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @TABLE t

returns:

ID  List_Output
1    XX, ABC, ABCD

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

First append comas at the ends using concat(), so that each element is enclosed by commas. Then replace the element using replace() including the surrounding commas. Finally use substring() to get the string without the commas at the begin and end.

UPDATE elbat
       SET list = substring(replace(concat(',', list, ','), ',AB,', ',XX,'), 2, len(replace(concat(',', list, ','), ',AB,', ',XX,')) - 2);

db<>fiddle

Upvotes: 2

Related Questions