Replace A String In SQL Server Table Column


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.
The obvious reason is that we were recording the uploaded file URL in the table column, which is now changed.
For example:
Replace A String In SQL Server Table Column
Previous File URL:
New File URL:
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
  1. update tablename     
  2. set fileUrl= replace(fileUrl, 'https://oldSiteName''https://newsiteName')     
  3. where     
  4. fileUrl like 'https://oldSiteName%'    
  5. 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:
  1. UPDATE tablename   
  2. SET   
  3. fileUrl = REPLACE(CAST(fileUrl AS nvarchar(max)), 'https://oldSiteName''https://newsiteName')  
  4. where   
  5. CAST(fileUrl AS nvarchar(max)) LIKE 'https://oldSiteName%'