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