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:

New File Url:

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%'


By Rijwan Ansari

Research and Technology Lead | Software Architect | Full Stack .NET Expert | Tech Blogger | Community Speaker | Trainer | YouTuber. Follow me @ Md Rijwan Ansari is a high performing and technology consultant with 10 plus years of Software Development and Business Applications implementation using .NET Technologies, SharePoint, Power Platform, Data, AI, Azure and cognitive services. He is also a Microsoft Certified Trainer, C# Corner MVP, Microsoft Certified Data Analyst Associate, Microsoft Certified Azure Data Scientist Associate, CSM, CSPO, MCTS, MCP, with 15+ Microsoft Certifications. He is a research and technology lead in Tech One Global as well as leading Facebook community Cloud Experts Group and SharePoint User Group Nepal. He is a active contributor and speaker in community, C# Corner MVP and his rank at 20 among 3+ millions members. Additionally, he is knee to learn new technologies, write articles, love to contribute to the open-source community. Visit his blog RIJSAT.COM for extensive articles, courses, news, videos and issues resolution specially for developer and data engineer.

One thought on “Replace a string in SQL server Table Column”

Leave a Reply

Your email address will not be published. Required fields are marked *