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:

What does the SQL replace function do in SQL Server?
How to use Collate function with REPLACE in SQL Server?
How to replace multiple characters in SQL?
How to perform an update using the REPLACE in SQL Server?
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.

1	SELECT REPLACE('SQL Server vNext','vNext','2017') SQL2017;

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

1	SELECT REPLACE('SQL Server vNext' COLLATE Latin1_General_CS_AS,'vnext','2017') SQL2017;

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

1	SELECT REPLACE('SQL Server vNext' COLLATE Latin1_General_CI_AS,'vnext','2017') SQL2017;

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}.

1	SELECT REPLACE(REPLACE(REPLACE(REPLACE('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.

You can refer to the article Top SQL String functions in SQL Server 2017 for more information.

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

DROP TABLE IF EXISTS #temp;

CREATE TABLE #temp

(name NVARCHAR(50),

GRP NVARCHAR(100)

);

INSERT INTO #temp

VALUES

('Prashanth',

'AB'

('Kiki',

'ABC'

('Steven',

'ABCD'

);

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.

SELECT Name,

GRP,

REPLACE (REPLACE (REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4) GRPCODE

FROM #temp;

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

UPDATE #temp

SET

GRP = replace(replace(REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4);

Now, let’s take a look at the data

1 2	SELECT * FROM #temp;

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.

DECLARE @searchPattern VARCHAR(100)= 'employee';

SELECT DISTINCT

'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

FROM syscomments

WHERE TEXT LIKE '%'+REPLACE(REPLACE(@searchPattern, ']', '\]'), '[', '\[')+'%' ESCAPE '\'

ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ';

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.

UPDATE @sptemp

SET

storedProcedureText = REPLACE(REPLACE(storedProcedureText, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), @searchpattern, @replacestring);

SELECT storedProcedureText

FROM @sptemp;

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.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @searchpattern VARCHAR(100)= 'employee', @replacestring VARCHAR(100)= 'Prashanth';

-- this will hold stored procedures text

DECLARE @sptemp TABLE(storedProcedureText VARCHAR(MAX));

DECLARE cur CURSOR FAST_FORWARD

FOR SELECT DISTINCT

'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

FROM syscomments

WHERE TEXT LIKE '%'+REPLACE(REPLACE(@searchpattern, ']', '\]'), '[', '\[')+'%' ESCAPE '\'

ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ';

--Open Cursor

OPEN cur;

FETCH NEXT FROM cur INTO @sql;

WHILE @@FETCH_STATUS = 0

BEGIN

--stored procedure script into a column

INSERT INTO @sptemp

EXEC (@sql);

-- Add GO keyword

INSERT INTO @sptemp

VALUES('GO');

FETCH NEXT FROM cur INTO @sql;

END;

CLOSE cur;

DEALLOCATE cur;

UPDATE @sptemp

SET

storedProcedureText = REPLACE(REPLACE(storedProcedureText, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), @searchpattern, @replacestring);

SELECT storedProcedureText

FROM @sptemp;

Author
Recent Posts

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

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

SQLShack

Overview of the SQL REPLACE function

Using the Collate function with REPLACE

How to replace multiple patterns in a given string

Use-Case

Appendix

Using the Collate function with REPLACE

How to replace multiple patterns in a given string

Use-Case

Appendix

Related posts: