SQL REPLACE Function Overview By Practical Examples
Summary: in this tutorial, you will learn how to use the SQL REPLACE() function with syntax and examples to search and replace all occurrences of a substring with a new substring by a string.
SQL REPLACE Function Overview
Sometimes, you would like to search and replace a substring with a new one in a column e.g., modification or change a dead link to a new one, rename an obsolete product to the new name, etc.
SQL Server provides a very useful string function known as REPLACE. this function allows you to replace all occurrences of a substring with a new substring by a string.
This function is a String Function, which is used to replace the existing(present) string with a new string value. Basically, this Function returns VARCHAR output. however if one of the input values is NVARCHAR, then the string SQL function will return NVARCHAR output.
REPLACE(input_string, substring, new_substring);
In this syntax:
string: Required. it is an original string or any string expression to be searched.
old_substring: Required. it is the substring to be replaced.
new_substring: Required. it is the replacement string.
Important Note: The SQL Server REPLACE() function returns a new string in which all occurrences of the substring are replaced by the new_substring. if any argument is NULL then it returns NULL
Let’s see some examples and explore how to use.
SELECT REPLACE('Tutorial scan',' ','X');
SQL REPLACE Use Cases
Generally, when you migrate a database, the dependent objects additionally have to be compelled to be migrated. Let us discuss in a little additional depth the method of handling such changes dynamically using the SQL REPLACE function with T-SQL constructs.
For one database migration project, therefore, there are multiple linked server configurations. The linked servers are mentioned in multiple stored procedures. It’s a matter of reality to search or update the stored procedure scripts however the intent is to automate the entire method and make sure that there’s no need for manual updates.
Now you will use the loop to go through the listed objects and also generate the script and store the result in the temp table.
Perform update to change the keyword from CREATE to ALTER. during this means, the script is prepared to execute on the target database. In a few cases, there’s a requirement for retaining the script.
This is the correct time to copy and paste the new query window and make sure that everything appearance fine to run the SQL. You’ll also automate the process using stored procedures in SQL.
It is a nested function and it is called multiple times to replacement the corresponding string as per the defined positional values within the REPLACE SQL Server function.
In the example, we can use SQL TRANSLATE, a new SQL Server 2017 function. The following query replaces the pattern X, Y, and Z with the values 6, 8, and 7 and generates a new column named GRPCODE.
--DROP TABLE IFEXISTS#demotable;
The below SQL function statement undergoes an execution of 3 iterations to get the desired result. The 1st, input pattern ‘X’ is evaluated and if found, 6 are changed. The 2nd, Y is evaluated. If found the numeric value 8 is changed. Finally, Z is changed by 7.