SQL Server: Replace Function

  • Article

This document was originally published as Tech Blog Content and has been reproduced here to allow the community to correct any inaccuracies or provide other enhancements before updating the original version of this topic

Introduction

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

Table

My table contains 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.

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 1

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

Example 2

How to put space between "BlogContent" like "Blog Content" without affecting whole content in the database.

Replace Function

You can use a single space. Like 'Content' to ' Content'.

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

Output 2

Summary

We learned how to use a replace function in Microsoft SQL. I hope this article is useful for all .NET programmers.