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 (value, pattern, replacement)
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:
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’.
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.
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.
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.
