UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column.

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

When I execute the script SQL Server does not report an error, but it does not update anything either. Why is that?

Solution 1:

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 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

Solution 2:

Try to remove % chars as below

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

Solution 3:

To make the query run faster in big tables where not every line needs to be updated, you can also choose to only update rows that will be modified:

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

Solution 4:

query:

UPDATE tablename 
SET field_name = REPLACE(field_name , 'oldstring', 'newstring') 
WHERE field_name LIKE ('oldstring%');