Replace specific numbers in comma-separated list-CodePudding

I'm trying to replace a combination of numbers using the REPLACE function in SQL Server but a specific ones inside a specific row. For example: row value = '17,171,217,317,28' and will do it like this:

UPDATE TABLENAME SET COLUMN_NAME = REPLACE(column value,'17,','')

but that will replace all the 17 found it in the column row.

How do I specify to only replace the starting 17 found in the row?

CodePudding user response：

Try using this:

UPDATE TABLENAME SET COLUMN_NAME = REPLACE(',' column value,',17,','')

Note that I agree not to store your data like that, violates 1NF (First Normal Form)

CodePudding user response：

If I understand the requirement correctly:

DECLARE @ReplaceValue varchar(32) = '17,';

UPDATE dbo.TABLENAME 
  SET COLUMN_NAME = STUFF(COLUMN_NAME, 1, LEN(@ReplaceValue), '')
  WHERE COLUMN_NAME LIKE @ReplaceValue   '%';

Example db<>fiddle