CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-administration
Iralda Mitro
Iralda Mitro

Reputation: 7356

How to replace a string in a SQL Server Table Column

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?

Upvotes: 412

Views: 921792

Answers (10)

user11441779
user11441779

Reputation:

you need to replace path with the help of replace function.

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

here column_name refers to that column which you want to change.

Hope it will work.

Upvotes: 9

Shekhar Patel
Shekhar Patel

Reputation: 671

You also can replace large text for email template at run time, here is an simple example for that.

DECLARE @xml NVARCHAR(MAX)
SET @xml = CAST((SELECT [column] AS 'td','',        
        ,[StartDate] AS 'td'
         FROM [table] 
         FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
select REPLACE((EmailTemplate), '[@xml]', @xml) as Newtemplate 
FROM [dbo].[template] where id = 1

Upvotes: 0

Basheer AL-MOMANI
Basheer AL-MOMANI

Reputation: 15327

all answers are great but I just want to give you a good example 

select replace('this value from table', 'table',  'table but updated')

this SQL statement will replace the existence of the word "table" (second parameter) inside the given statement(first parameter) with the third parameter

the initial value is this value from table but after executing replace function it will be this value from table but updated

and here is a real example 

UPDATE publication
SET doi = replace(doi, '10.7440/perifrasis', '10.25025/perifrasis')
WHERE doi like '10.7440/perifrasis%'

for example if we have this value

10.7440/perifrasis.2010.1.issue-1

it will become

10.25025/perifrasis.2010.1.issue-1

hope this gives you better visualization

Upvotes: 13

Nitika Chopra
Nitika Chopra

Reputation: 1405

You can use this query 

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

Upvotes: 15

Durgesh Pandey
Durgesh Pandey

Reputation: 2412

select replace(ImagePath, '~/', '../') as NewImagePath from tblMyTable

where "ImagePath" is my column Name.
"NewImagePath" is temporery column Name insted of "ImagePath"
"~/" is my current string.(old string)
"../" is my requried string.(new string)
"tblMyTable" is my table in database.

Upvotes: 6

khichar.anil
khichar.anil

Reputation: 4964

If target column type is other than varchar/nvarchar like text, we need to cast the column value as string and then convert it as:

update URL_TABLE
set Parameters = REPLACE ( cast(Parameters as varchar(max)), 'india', 'bharat')
where URL_ID='150721_013359670'

Upvotes: 4

cjk
cjk

Reputation: 46415

It's this easy:

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

Upvotes: 695

Igor Bakay
Igor Bakay

Reputation: 191

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.

Upvotes: 19

Caesar
Caesar

Reputation: 385

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'

Upvotes: 32

Marc Gravell
Marc Gravell

Reputation: 1062502

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

Upvotes: 150

Related Questions