Replace function in SQL Server with examples

In this article we will discuss the definition and syntax of replace function along with examples like simple usage of replace, string found multiple times, no string found, passing INT data as string to replace, replace with update statement and where condition, replacing single quote with double quote and using of nested replace function.

"REPLACE is a SQL server function which replaces the specified string with another string."

Syntax:

REPLACE(string1,string2,string3)

string1 - string to be searched

string2 - string to be found

string3 - string to be replaced.

string1, string2 or string3 can be of type character or binary data type.

Return Type:

·         nvarchar -if one of the input arguments is of the nvarchar data type

·         varchar – argument datatype as other than nvarchar data type

·         NULL - if any one of the arguments is NULL.

Let’s understand with simple example

SELECT REPLACE('DotNetMirror','dot','.')

Output - .NetMirror

Explanation: The searched string “dot” is found once in  'DotNetMirror’ so it will replace dot string with . and returns “.NetMirror ”

String found multiple times:

If string2 found multiple times in string1 then it will replaces all the occurrences which are matched with string3 in string1.

SELECT REPLACE('DotNetMirror DotNetMirror','dot','.')

Output - .NetMirror .NetMirror

Explanation: The searched string “dot” is found 2 times in  'DotNetMirror DotNetMirror' so it will replace dot string with . and returns “.NetMirror .NetMirror”

No String found:

If none of string found in string1 then it will return the orginal string i.e. string1 

SELECT REPLACE('DotNetMirror','reflects','.')

Output: -  DotNetMirror

Explanation: Because “reflects”string is not found in DotNetMirror so it will not replace any content with .(dot) and returns original value

 Passing INT value to be searched in string:

We can pass INT values in string2 and string3 that will also replaces if the string matches. 

SELECT REPLACE('DotNetMirror 456','456','123')

Output: -  DotNetMirror 123

Explanation:  The searched string “456” is found once in  'DotNetMirror 456’ so it will replace 456 string with 123 and returns DotNetMirror 123”

Replace with update statement

In some scenarios we need to replace a string in a table column data so we can use below query.

UPDATE Student SET FirstName=REPLACE(FirstName, 'Kumar', 'Kumar sir')

Output: - replaces first name column data with ‘kumar ji’ if any of string contrains kumar in column data.

Replace with update statement and where condition:

The above statement replaces all the matched occurrences in the column data in order to update for particular columns we can replace function in update statement with where condition.

E.g :

UPDATE Student SET FirstName=replace(FirstName, 'Kumar', 'Kumar sir') WHERE Country='India'

Output: - replaces first name column data with ‘kumar ji’ if any of string contrains kumar in FirstName column data for students those belongs to country India.

Replace single-quote (') with double-quote ("):

In order to get a single quote in a string we need to give two single quotes.

select replace('''DotNETMirror''', '''', '"')

Output: "DotNETMirror"

Using nested replace:

select replace(REPLACE('DotNetMirror','dot','.'),'.','Dot')

Output: DotNetMirror

Explanation: From inner replace method dot is replaced with .(dot)  which returns .NetMirror and again from outer replace function .(dot) is replaced with Dot so it returns the original value “DotNetMirror”