Replace a value in a comma separated string in SQL Server database

Question

I'm using a SQL Server 2014 database and I have a column that contains comma-separated values such as:

1,2,3
4,5
3,6,2
4,2,8
2

What I need to do is to replace the number 2 with the number 3 (string values) in each record and not duplicate the 3 if possible. I'm not sure that this can be done unless I use a function and I'm not sure how to do it in a function.

I think I need to split a string into a table and then loop the values and put it back together with the new value. Is there an easier way? Any help is appreciated.

Expect output would therefore be:

1,3
4,5
3,6
4,3,8
3

Never, ever store data as comma separate items, it will only cause you lots of trouble. — jarlh
– jarlh, Commented Dec 15, 2017 at 12:00
instead of 1,2,3 I need 1,3 and instead of 3,6,2 just 3,6 and 4,2,8 becomes 4,3,8 and the last 2 becomes 3. The number three can change based upon the situation- it may be a 4 or an 8- etc — Bill
– Bill, Commented Dec 15, 2017 at 12:09
@jarlh I don't completely agree with that; as in the "never, ever store". There are plenty of ways of extracted delimited values in SQL Server. I still don't recommend it, but there are cases where you may be forced to do so, much like there are times when you don't follow 3NF/5NF. — Thom A
– Thom A ♦, Commented Dec 15, 2017 at 12:09
So you need to read about database normalisation and why you should use it. It may have worked for years. It will not have worked as well as it could or should. Storing data like this makes it harder to use, at a minimum. — underscore_d
– underscore_d, Commented Dec 15, 2017 at 12:43

Salman Arshad · Accepted Answer · 2017-12-15 14:08:25Z

While it is possible, I do not encourage this:

DECLARE @old AS VARCHAR(3) = '2';
DECLARE @new AS VARCHAR(3) = '3';

WITH opdata(csv) AS (
    SELECT '1,22,3' UNION ALL
    SELECT '1,2,3'  UNION ALL
    SELECT '4,5'    UNION ALL
    SELECT '3,6,2'  UNION ALL
    SELECT '4,2,8'  UNION ALL
    SELECT '2'
), cte1 AS (
    SELECT
        csv,
        CASE
        WHEN ',' + csv + ',' LIKE '%,' + @old + ',%' THEN
            CASE
            WHEN ',' + csv + ',' LIKE '%,' + @new + ',%' THEN REPLACE(',' + csv + ',', ',' + @old + ',', ',')              -- new already present so just delete old
            ELSE                                              REPLACE(',' + csv + ',', ',' + @old + ',', ',' + @new + ',') -- replace old with new
            END
        ELSE ',' + csv + ','
        END AS tmp
    FROM opdata
)
SELECT
    csv,
    STUFF(STUFF(tmp, 1, 1, ''), LEN(tmp) - 1, 1, '') AS res
FROM cte1

Result:

csv    | res
-------+-------
1,22,3 | 1,22,3
1,2,3  | 1,3
4,5    | 4,5
3,6,2  | 3,6
4,2,8  | 4,3,8
2      | 3

Note that the plethora of ',...,' is required to avoid replacing values such as 22. If you are using SQL Server 2017 you can ditch the extra CTE + STUFF and use TRIM(',' FROM ...).

Thom A · Accepted Answer · 2017-12-15 12:16:30Z

1

This isn't going to perform particularly well, however:

WITH CTE AS (
    SELECT *
    FROM (VALUES ('1,2,3'),
                 ('4,5'),
                 ('3,6,2'),
                 ('4,2,8'),
                 ('2')) V(DS))
SELECT CASE WHEN DS LIKE '%3%' THEN REPLACE(REPLACE(DS,'2,',''),',2','')
            WHEN DS LIKE '%2%' THEN REPLACE(DS,'2','3')
            ELSE DS
       END
FROM CTE;

answered Dec 15, 2017 at 12:16

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

1 Comment

Bill Over a year ago

Thanks I'll give that a try

Nitesh Kumar · Accepted Answer · 2017-12-15 12:55:49Z

1

May be you are looking something like this.

SELECT REPLACE(CASE WHEN CHARINDEX('2', '1,2,3') > 0 THEN REPLACE('1,2,3', '2','') ELSE REPLACE('1,2,3', '2','3') END, ',,',',')

I have taken a hard coded value for demonstration. You can replace'1,2,3' with column name in the table.

answered Dec 15, 2017 at 12:55

Nitesh Kumar

1,7744 gold badges19 silver badges27 bronze badges

1 Comment

Bill Over a year ago

I like that-- looks interesting and I will give it a try

gofr1 · Accepted Answer · 2017-12-15 13:48:49Z

0

To update:

DECLARE @was nvarchar(2) = 2,
        @willbe nvarchar(2) = 3,
        @d nvarchar(1) = ','

UPDATE strings
SET string = REVERSE(
                    STUFF(
                        REVERSE(
                            STUFF(
                                CASE WHEN CHARINDEX(@d+@willbe+@d,@d+string+@d) > 0 
                                    THEN REPLACE(@d+string+@d,@d+@was+@d,@d) 
                                    ELSE REPLACE(@d+string+@d,@d+@was+@d,@d+@willbe+@d) 
                                        END,1,1,'')
                                    ),1,1,''))

Output:

1,3
4,5
3,6
4,3,8
3

edited Dec 15, 2017 at 13:48

answered Dec 15, 2017 at 13:30

gofr1

16k11 gold badges47 silver badges56 bronze badges

Collectives™ on Stack Overflow

Replace a value in a comma separated string in SQL Server database

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related