How to compare a comma separated string with a string and replace with the string with comma separat-CodePudding

I have a column A which contains comma separated values for ex. "5,6,7" and another column B contain "5".

I have to compare column B value with column A value and replace with a certain value. In this case i want to replace "5,6,7" with "8,6,7".

Is this possible with Sql server

Thank you.

CodePudding user response：

You can use STRING_SPLIT to split up the values, then REPLACE and STRING_AGG to reaggregate

SELECT B = (
    SELECT STRING_AGG(REPLACE(value, '5', '8'), ',')
    FROM STRING_SPLIT(YourTable.A, ',')
)
FROM YourTable;

CodePudding user response：

Another option is using the REPLACE() function:

SELECT A = STUFF(
   REPLACE((','   A), (','   B   ','), ',8,'), 
   1, 1, ''
)
FROM (VALUES
   ('5,6,7', '5')
) v (A, B)

Result:

A
-----
8,6,7