🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask 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?
string sql-server sql
by

3 Answers

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


UPDATE tablename 

SET field_name = REPLACE(field_name , 'oldstring', 'newstring') 

WHERE field_name LIKE ('oldstring%');
kshitijrana14
Try to remove % chars as below
UPDATE dbo.xxx

SET Value = REPLACE(Value, '123', '')

WHERE ID <=4

Login / Signup to Answer the Question.

LoginSignup