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.
REPLACE function is used for string manipulation in SQL. It replaces string patterns within a string with a new string.
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’.
/* 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.
/* 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.
/* 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.