Query to use update statement with Replace function in SQL Server

Introduction:

In this article I will explain how to write query to use update statement with Replace function in SQL Server.

Description:

In previous posts I explained Replace function example and Substring function example in SQL Server and many more articles relating to SQL Server. Now I will explain how to use update statement with Replace function in SQL Server. In one application I got requirement like update particular part of the string with another value. To achieve this functionality I realized it’s better to use Replace() function with update query.

To know about Replace function check this post Replace() function example in SQL Server

Generally our Replace() Function declaration of replace function will be like this

SELECT REPLACE(' Dasari Suresh', 'Dasari', 'Aspdotnet')

Output: 'Aspdotnet Suresh'

In above query we declared string as 'Dasari Suresh', and we tried to replace string part 'Dasari' to 'Aspdotnet' string value.

From above example we learned that Replace() function is used to replace particular part of string based on our requirement. Now I will explain how to use update statement with replace function.

I have a one table with some data in that I need to update particular part of column value with some other value.

Example

I have table userdetails like this

UserName	Location	Website
Suresh Dasari	Chennai	http//www.aspdotnet-suresh.com
Mahesh	Vijayawada	http//www.usefulcorner.com
Mahendra	Guntur	http//www.test.com

If you observe above table column “Website” it contains urls but those are not in correct format. Generally our website urls will be like this “http://www.” but here all the urls will be like this “http//www.”

Now I need to update Website column data (url format) to correct format for that we need to write update query with replace function to modify particular part of column that would be like this

UPDATE SampleDetails SET Website=REPLACE(Website,'http//www.','http://www.')

Once we run above query our table Website column data will be like this 

UserName	Location	Website
Suresh Dasari	Chennai	http://www.aspdotnet-suresh.com
Mahesh	Vijayawada	http://www.usefulcorner.com
Mahendra	Guntur	http://www.test.com

By using replace function in update query we can update certain part of data easily.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email