Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

SQL REPLACE Function (Transact SQL)



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.


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:

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

And the result we can provide to the suppliers is:


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:

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

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


REPLACE (input_expression, substring_pattern, new_value)


  • 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.


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

More from the blog

MySQL PostgreSQL SQLite SQL Server