Over a million developers have joined DZone.

Replace Function in Microsoft SQL Server

Almost everyone is familiar with C#, and the handy replace function. Here's how to use this function in Microsoft SQL.

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

Introduction

Mostly everyone is familiar with the C# replace function. In this article I will explain the use of the replace function in Microsoft SQL Server, which is a simple function to replace a bulk amount of data in the database. I will also give some examples.

Table

My table contains a bulk amount of data and some of the values are wrongly entered  into the database. So I want to change those particular values into the table using a single query.

The given table contains incorrect address in the “Address1” column like httpp://www.c-sharpcorner.com/, so http is appended with an extra p (“httpp“). So we can update those values in a single query. How ? Microsoft SQL Server provides Replace function to replace particular data.

Image title

Sql Query:

The following code will replace that particular data.

update dbo.Tbl_Replace set Address1 = Replace(Address1,'httpp','http')

We can directly use the update method in the above query. Why we are using replace function in SQL?

Because when you are trying to update bulk amount of data you can use the replace function. It will consume the time of update, since you don’t have to update each and every address separately.

update dbo.Tbl_Replace set Address1 = 'http://www.c-sharpcorner.com/' where Id=1  

The above update code only changes the particular Id value.

Example 1

Replace Function

The Sql Replace function replacing “httpp” to “http”:

update dbo.Tbl_Replace set Address1 = Replace(Address1,'httpp','http') 

Output

The Replace function replaced the appended value using a single query:

Image title

Example 2

Table

How to put space between “BlogContent” like “Blog Content” without affecting whole content in the database:

Image title

Replace Function

You can use a single space.Like ‘Content’ to ‘ Content’

update dbo.Tbl_Replace set Details = Replace(Details,'Content',' Content') 
Output

Image title

I hope this article is useful for all .NET programmers.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

Topics:
sql ,sql server ,microsoft

Published at DZone with permission of Rajeesh M R (Rajeesh Menoth). See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}