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.

Syntax:

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

Example

Let’s see some examples and explore how to use.
For example:


SQL REPLACE

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.

There are the following commonly asked questions:

How to use perform a simple REPLACE

The following SQL uses the REPLACE keyword to find a matching pattern string and replace it with another string.


simple REPLACE Function

How to use the Collate function with REPLACE in SQL Server?

The following SQL uses the collation function to validate the expression.


collate function

How to replace multiple characters in SQL?

The following illustration uses the replacement function to change multiple patterns of the expression 6*[9+8]/{5-9}.

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.

multiple characters output

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.

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.


output

How to perform an update using the REPLACE in SQL Server?

Here is an example to perform an update using this function to perform statement. In this case, as per the above SQL Code GRP column with the GRP CODE, run the following SQL.

Furthermore, let’s take a look at the data
UPDATE SQL OUTPUT

Conclusion:

In this tutorial, you will learned How to search and replace all occurrences of a substring with a new substring by a string and different asked question and answer with practical example.