UPDATE and REPLACE part of a string

Question

A table I have has two columns: ID and Value. A portion of certain strings in the second column need to be changed.

Example of Table:

ID            Value
---------------------------------
1             c:\temp\123\abc\111
2             c:\temp\123\abc\222
3             c:\temp\123\abc\333
4             c:\temp\123\abc\444

Now the 123\ in the Value string is not needed. I tried UPDATE and REPLACE:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '%123%', '')
WHERE ID <= 4

SQL Server does not indicate an error when I run the script, but it also does not update anything. How come?

Answer 1

The following should function because you don't need wildcards in the REPLACE command because it merely detects the string you supply as the second argument:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is typed text or ntext you need to cast it to nvarchar.

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

I hope this helps you.