CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2014
Bill
Bill

Reputation: 1477

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

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

Upvotes: 1

Views: 2654

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272396

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 ...).

Upvotes: 3

gofr1
gofr1

Reputation: 15997

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

Upvotes: 0

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

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.

Upvotes: 1

Thom A
Thom A

Reputation: 96016

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;

Upvotes: 1

Related Questions