Posted By: Anonymous
I’ve got a table with two columns,
Value. I want to change a part of some strings in the second column.
Example of Table:
ID Value --------------------------------- 1 c:temp123abc111 2 c:temp123abc222 3 c:temp123abc333 4 c:temp123abc444
123 in the
Value string is not needed. I tried
UPDATE dbo.xxx SET Value = REPLACE(Value, '%123%', '') WHERE ID <= 4
When I execute the script SQL Server does not report an error, but it does not update anything either. Why is that?
You don’t need wildcards in the
REPLACE – it just finds the string you enter for the second argument, so the following should work:
UPDATE dbo.xxx SET Value = REPLACE(Value, '123', '') WHERE ID <=4
If the column to replace is type
ntext you need to cast it to nvarchar
UPDATE dbo.xxx SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '') WHERE ID <=4