June 4, 2021

SQL REPLACE function evaluates an input string for a specified substring pattern and replaces these substrings with another specified string value. Use it to substitute one set of characters for another with a character based value.

Description

The REPLACE function’s purpose is string manipulation. It replaces string patterns within a string with a new string.

The REPLACE function is good when you wish to find one or more characters in a sting and replace them with other characters.  A common application is to replace all dashes ‘-‘  with spaces.

Here is the general form of the REPLACE function

REPLACE (valuepatternreplacement)

Where value is the string to work with, pattern is the portions of the string you wish to find and replace, and replacement is the value to replace the pattern.

If any of the parameters are NULL then REPLACE returns NULL.

If pattern isn’t found, nothing is replaced, and value is returned in its original form.

In business you’ll come across data from two separate systems, such as your systems and a customer or supplier’s systems where data is treated differently.  This can cause issues, especially when it comes to matching.

Once common issue I’ve seen occurs with part numbers.  Consider Adventure Works.  Within the company, part numbers are formatted with dashes, such as ‘AR-5381’; however, some suppliers have replaced the dashes with spaces like so ‘AR 5381’.

Before we do a large scale data match the production team wishes to provide the suppliers with our parts list with the dashes replaced with spaces.

Here is a query we could run to do so:

SELECT Name,
       ProductNumber,
       REPLACE(ProductNumber,'-',' ') as SupplierProductNumber
FROM   Production.Product

And the result we can provide to the suppliers is:

REPLACE Results

Though you can’t have blank pattern, and if you think about it, that wouldn’t’ make too much sense, you can replace a pattern with a blank value.  Why?  Well, a good reason is to strip characters out of a string.

In the ProductNumber example, There may be cases were we want to use the product number without dashes.  This query could be used in that case:

SELECT Name,
       ProductNumber,
       REPLACE(ProductNumber,'-','') as SupplierProductNumber
FROM   Production.Product

Notice that two single quotes together as ” represents an empty string.

Syntax

REPLACE (input_expression, substring_pattern, new_value)

SQL REPLACE Usage Notes

  • The first argument of a SQL REPLACE function is a character expression. It can be of a character or a binary data type. The REPLACE function evaluates this expression to find matching substrings.
  • The second argument in REPLACE function is the value that is to be replaced in the input character expression. It is a substring pattern. If the substring pattern is an empty string, then REPLACE returns the input expression unchanged.
  • The final argument is the new value that replaces the substring pattern in the input expression. It can be of a character or binary data type.
  • REPLACE function returns a varchar data type.
  • If any of the input arguments is nvarchar, then the function returns a nvarchar value.
  • If any of the input arguments is NULL, then it returns NULL.
  • When the expression is not varchar or nvarchar, then the REPLACE function returns the first 8000 bytes of the return value.
  • For values greater than 8000 characters, REPLACE casts input expression to a large data type.

SQL REPLACE Examples

The following query uses REPLACE function to replace the ‘abc’ substring in the input expression with a new value of ‘xyz’.

SELECT REPLACE('replace abc with b','abc','xyz') NewString;
/* Answer */
SELECT REPLACE('replace abc with b','abc','xyz') NewString; 

The following query uses the AdventureWorks2019 database. It replaces the email address of all employees from the adventure-works domain to adventureworks. In this way, string values are manipulated using the REPLACE function.

SELECT REPLACE(EmailAddress, 'adventure-works', 'adventureworks' ) new_EmailAddress FROM Person.EmailAddress
/* Answer */
SELECT REPLACE(EmailAddress, 'adventure-works', 'adventureworks' ) new_EmailAddress
FROM Person.EmailAddress

We can permanently change these email address values in the database using update statements. Multiple column values in the database are changed to new values using REPLACE function. You can use REPLACE in combination with WHERE to target specific rows.

SELECT REPlACE(City,'Edmonds', 'NewYork') FROM Person.Address WHERE AddressID = 150;
/* Answer */
SELECT REPlACE(City,'Edmonds', 'NewYork')
FROM Person.Address
WHERE AddressID = 150;

The query replaces the city of a specific person with address ID 150 to a new city. Using an update statement will permanently reflect the changes in the database.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>