SQL Server Articles

Submit Article
Home » Articles » SQL Server » String FunctionsRSS Feeds

REPLACE Function

Posted By: Sarita Patel     Category: SQL Server     Views: 46391

This article explains REPLACE function of sql server with examples.

REPLACE is used to replace part of a given string with specified value. 

Syntax of REPLACE Function :

REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

string_expression1 is a string to be replaced.

string_expression2 is a string to be searched from string_expression1. If you specify characters that are not in string_expression1 then it returns string without replacing i.e. specified string in string_expression1. 

string_expression3 is a new value provided to replace string_expression2 from string_expression1. 

It returns either character data type or binary data type.

Length of string_expression2 and string_expression3 can vary i.e. not necessary to have same length.

Replace function replaces all occurrence of string_expression2 from string_expression1 by string_expression3.

Examples of REPLACE Function :

Example 1 : Use of REPLACE function in select clause

SELECT REPLACE('Syntax-Example', 'Example','Explanation') 


Above example returns new string by replacing 'Example' by 'Explanation' from specified string. 

Example 2 : Use of REPLACE function to display field value of table in a select clause

SELECT ContactName, REPLACE(ContactName,'an','en') AS 'New Name'
FROM    Customers

ContactName                  New Name
Maria Anders                  Maria enders
Ana Trujillo                     ena Trujillo
Antonio Moreno              entonio Moreno
Thomas Hardy                Thomas Hardy
Christina Berglund           Christina Berglund
Hanna Moos                    Henna Moos

Above example replaces customers name containing 'an' by 'en' from customers table.


Didn't find what you were looking for? Find more on REPLACE Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of REPLACE Function is from United States.
View All Articles

Related Articles and Code:

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!