Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Query to use update statement with Replace function in SQL Server

Jun 12, 2012
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 RSS subscribe by email Subscribe by Email

1 comments :

Anonymous said...

Hi sir, u gave table name as "userdetails" and queried as UPDATE "SampleDetails"... Make the modification and thanks for all your works...

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.