aston_zh aston_zh - 1 month ago 9
SQL Question

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?

Answer

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

(I also added the \ in the replace as I assume you don't need that either)

Comments