How to replace a string in a SQL Server Table Column

Question

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change.

In the path column, I have many records and I need to change just a portion of the path, but not the entire path. And I need to change the same string to the new one, in every record.

How can I do this with a simple update?

Solution 1

It's this easy:

update my_table
set path = replace(path, 'oldstring', 'newstring')

Solution 2

UPDATE [table]
SET [column] = REPLACE([column], '/foo/', '/bar/')

Solution 3

I tried the above but it did not yield the correct result. The following one does:

update table
set path = replace(path, 'oldstring', 'newstring') where path = 'oldstring'

Solution 4

UPDATE CustomReports_Ta
SET vchFilter = REPLACE(CAST(vchFilter AS nvarchar(max)), '\\Ingl-report\Templates', 'C:\Customer_Templates')
where CAST(vchFilter AS nvarchar(max)) LIKE '%\\Ingl-report\Templates%'

Without the CAST function I got an error

Argument data type ntext is invalid for argument 1 of replace function.

Solution 5

You can use this query

update table_name set column_name = replace (column_name , 'oldstring' ,'newstring') where column_name like 'oldstring%'

View more solutions

Comments

[Derek Tomes]

I'd typically add where path like '%oldstring%' if there was a lot of data.

[Ian]

This totally defeats the purpose of using the replace method. You can accomplish the same thing like this: update table set path='newstring' where path='oldstring';

[v010dya]

perhaps you meant where path like '%oldstring%'?

[iMalek]

where condition make sense because if I have 50 rows in table and if I am replacing 10 rows with replace function it affects all 50 rows, even though it replaces 10 rows if you dont have where condition. But if you have where condition like mentioned in above comment it only affects 10 rows.

[Alex from Jitbit]

sorry, not to be nitpicky after ten years but it's unclear from the answer if foo is being replaced or bar (sorry again)