Prashanth Jayaram

Overview of the SQL REPLACE function

November 27, 2018 by

In this article, I’ll show you how to find and replace data within strings. I will demonstrate how to use the function SQL REPLACE, where you look for a substring within a string, and then replace it.

This article answers the following commonly asked questions:

  1. What does the SQL replace function do in SQL Server?
  2. How to use Collate function with REPLACE in SQL Server?
  3. How to replace multiple characters in SQL?
  4. How to perform an update using the REPLACE in SQL Server?
  5. How to prepare T-SQL code to perform a REPLACE?

A few other string functions are discussed in the articles SQL Substring function overview and SQL string functions for Data Munging (Wrangling).

Syntax

REPLACE (Expression, pattern, replacement)

Expression: The input string value on which the replace function has to operate.

Pattern: The substring to evaluate and provides a reference position to the replacement field.

REPLACEment: REPLACEs the specified string or character value of the given expression.

Note: The SQL REPLACE function performs comparisons based on the collation of the input expression.

Examples

How to use perform a simple REPLACE

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

Here is the result set.

Using the Collate function with REPLACE

The following SQL uses the case-sensitive collation function to validate the expression within the SQL REPLACE function

The output is a direct input of the expression as it fails to validate the input pattern.

The following SQL uses the same example but case-insensitive collation function is used to validate the expression within the function

The output shows the values are matched irrespective of cases

How to replace multiple patterns in a given string

The following example uses the SQL replace function to replace multiple patterns of the expression 3*[4+5]/{6-8}.

We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL REPLACE function.

In the aforementioned example, we can use the TRANSLATE, a new SQL Server 2017 function. It’s a good replacement string function for the SQL REPLACE function.

The following query replaces the pattern A, C and D with the values 5, 9 and 4 and generates a new column named GRPCODE

The below SQL REPLACE function undergoes an execution of 3 iterations to get the desired result. The first, input pattern ‘A’ is evaluated and if found, 5 are replaced. The second, B is evaluated. If found the numeric value 9 is replaced. Finally, D is replaced by 4.

Here is an example to update using the SQL REPLACE function. In this case, GRP column with the GRP CODE, run the following SQL.

Now, let’s take a look at the data

Use-Case

In general, when you migrate a database, the dependent objects also need to be migrated. For example, a linked server or specific column values of specific tables that reference a renamed table. Let’s get in-depth about the process of handling such changes dynamically using the SQL REPLACE function with T-SQL constructs.

In one complex database migration project, the server has multiple Linked Server configurations. These linked servers were referred and mentioned in multiple stored procedures. It is a matter of the fact to find and replace the stored procedure scripts but the intent is to automate the entire process to make sure that we are not going to do any manual updates.

Let me take you through the simple steps.

Step 1:

In this case, the search pattern is employee. Also, you see that the custom escape character is used to escape special characters ‘[‘and ‘]’ in the search pattern.

We can see in the output that 13 objects are listed by satisfying the search condition employee.

Step 2:

Now, it is simple to loop through the listed objects and generate the script and store it a temp table.

Step 3:

Perform a simple update to change the keyword create to “ALTER”. This way the script is ready to execute on the target database. In some cases, you just need to retain the script. That is still fine but don’t run this step.

It’s time to copy and paste result into new query window then make sure everything looks good to run the SQL.

In the below output you can see that search pattern employee is replaced by Prashanth.

You can refer the complete code in the Appendix section.

Appendix

T-SQL code to replace a string in all intended Stored Procedures automatically.

Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

116 Views