Replace a string in SQL server Table Column

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

Let me be more specific, we had a application where we were storing the location of 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 from where user can view the uplad file of the application.

After 2 years, the client decided to update DNS (Doman) of their SharePoint Site. Think of 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 new domain, were unable to open. OOPS!!! 

The obvious reason is that we were recording the uploaded file url in 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 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%'

Mostly this query will do the update.

In case if you get any error like:

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

Then you need to use cast as shown:

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