Replace A String In SQL Server Table Column

Sometimes, we might have a requirement to update or replace a particular string in an SQL table column.

Introduction 

 

Sometimes, we might have a requirement of updating or replacing a particular string in the SQL table column.

 

Let me be more specific, we had an application where we were storing the location of the file in SQL table “AppFile” in column “fileUrl”. We were uploading the file into another DMS (SharePoint Library) and storing the file view URL into our table, where users can view the upload file of the application.

 

After 2 years, the client decided to update the DNS (Domain) of their SharePoint Site. Think of a worse situation, our application was working fine with new files uploaded with new domain file URL. However, the files which were uploaded before the update of the new domain were unable to open.

 

OOPS!!! 

 

The obvious reason is that we were recording the uploaded file URL in the table column, which is now changed.

 

For example:

 

 

Previous File URL: https://OldSite.com/sites/SiteName/Attachments/FileName.docx

 

New File URL: https://NewSite.com/sites/SiteName/Attachments/FileName.docx

 

So, here the situation comes to update the old file path, but only the domain, not the file name.

 

There are several useful queries to do this job.

 

Query 1

update tablename
set fileUrl= replace(fileUrl, 'https://oldSiteName', 'https://newsiteName')
where
fileUrl like 'https://oldSiteName%'
update tablename set fileUrl= replace(fileUrl, 'https://oldSiteName', 'https://newsiteName') where fileUrl like 'https://oldSiteName%'

Mostly this query will do the update.

 

In case if you get any error such as:

 

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

 

Then you need to use cast as shown below:

UPDATE tablename
SET
fileUrl = REPLACE(CAST(fileUrl AS nvarchar(max)), 'https://oldSiteName', 'https://newsiteName')
where
CAST(fileUrl AS nvarchar(max)) LIKE 'https://oldSiteName%'

Cheers!!!