Standardize Text with Multi Search and Replace Function in SQL Server

By:   |   Updated: 2024-06-21   |   Comments (3)   |   Related: > Functions User Defined UDF


Problem

In a previous tip, SQL Server Find and Replace Values in All Tables and All Text Columns, we walked through how to replace a single value with another single value for all text columns and all tables in a database. I have a similar need, but I need to replace multiple values with new values in one column. In this tip we look at a few examples of how this can be done.

Solution

Before we get started let's setup an example of how this could be done. Let's create two tables, the first table is our data table and the second table is our synonym table.

CREATE TABLE [dbo].[tblDescription](
[id] [int] NOT NULL,
[description] [varchar](MAX) NULL,
CONSTRAINT [PK_tblDescription] PRIMARY KEY CLUSTERED 
(
[id] ASC
)
) ON [PRIMARY] 
CREATE TABLE [dbo].[tblSynonym](
[id] [int] IDENTITY(1,1) NOT NULL,
[keyword] [varchar](50) NULL,
[replacement] [varchar](50) NULL,
CONSTRAINT [PK_tblSynonym] PRIMARY KEY CLUSTERED 
(
[id] ASC
)
) ON [PRIMARY]

Now let's populate the tables with some test data.

INSERT INTO dbo.tblDescription 
VALUES (1, 'This is a test to see how this works. We will replace some values with other values.')

INSERT INTO dbo.tblSynonym (keyword, replacement)
VALUES ('works', 'operates')
INSERT INTO dbo.tblSynonym (keyword, replacement)
VALUES ('replace', 'substitute')
INSERT INTO dbo.tblSynonym (keyword, replacement)
VALUES ('values', 'meanings') 

Example 1 - With a Cursor

The first thought for how to replace multiple values with new values is to use a cursor and cursor through each of the values replacing the old value with the new value. So based on this we have created a function that will cursor through the synonym table and do multiple replaces on the values.

Here is the code for the function.

CREATE FUNCTION dbo.fnReplaceWords (@body VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @keyword VARCHAR(50)
DECLARE @replacement VARCHAR(50)

DECLARE cur_replacement CURSOR FOR 
SELECT keyword, replacement FROM dbo.tblSynonym

OPEN cur_replacement

FETCH next FROM cur_replacement INTO @keyword, @replacement
WHILE @@FETCH_STATUS = 0
BEGIN
SET @body = REPLACE(@body, @keyword, @replacement)
FETCH next FROM cur_replacement INTO @keyword, @replacement
END

CLOSE cur_replacement
DEALLOCATE cur_replacement

RETURN @body
END 

Once the function has been created we can do a simple test using the following query.

SELECT description, dbo.fnReplaceWords(description) AS ReplacedDescription
FROM dbo.tblDescription 

This will create the following output. The first line has the original values and the second line shows where the values have been updated to their new values based on the synonym table.

This is a test to see how this works. We will replace some values with other values. 
This is a test to see how this operates. We will substitute some meanings with other meanings.

If we want to do an actual update we can run the following query.

UPDATE dbo.tblDescription
SET description = dbo.fnReplaceWords(description) 

Example 2 - Without a Cursor

Here is another approach that does not use a cursor. We still create a function to do the update, but this is a much simpler approach.

CREATE FUNCTION dbo.fnReplaceWords_2 (@body VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN

SELECT @body = REPLACE(@body,keyword,replacement)
FROM dbo.tblSynonym

RETURN @body
END 

Once the function has been created we can do a simple test using the following query.

SELECT description, dbo.fnReplaceWords_2(description) AS ReplacedDescription
FROM dbo.tblDescription 

This will create the following output. The first line has the original values and the second line below shows where the values have been updated to their new values.

This is a test to see how this works. We will replace some values with other values. 
This is a test to see how this operates. We will substitute some meanings with other meanings.

If we want to do an actual update we can run the following query.

UPDATE dbo.tblDescription
SET description = dbo.fnReplaceWords_2(description) 

So as you can see, here are two different approaches to the same problem. So the question is what is the better approach?

If we run both approaches and include the query stats and execution plans it looks like both approaches take the same amount of time and resources. This is probably because we have such a small set of data and keywords. Take the time to put a larger sample in place to see which option works better for you.

Next Steps
  • Keep in mind that both of these approaches do not handle words within words. So if you want to replace the word "date" and also want to replace the word "update" if you replace "date" first it will modify the words for both "date" and "update". One way to get around this is to order your keyword lookup based on size and value, but this is not 100% foolproof so you will need to keep an eye on what you are replacing. Also, you could include leading or trailing spaces if you want to get an exact match. It really depends on how your data is stored and what you want to update.
  • Use this technique to standardize keywords in your database or restrict certain words from being used.
  • Take some time to do a larger test to see if one approach works better than the other approach.
  • Also review these related tips:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-06-21

Comments For This Article




Thursday, May 31, 2018 - 1:52:37 AM - Dave Back To Top (76055)

Nice one,  dbo.fnReplaceWords_2 did just what i needed

 


Saturday, May 9, 2015 - 6:59:09 AM - Greg Robidoux Back To Top (37138)

Hi Paco, this is updating every row in the table and also doing a REPLACE for each set of words.  This is definitely not the fastest approach to doing something like this. 


Friday, May 8, 2015 - 6:40:07 PM - paco Back To Top (37137)

thsi query is very slowly with a data of 2 million and more















get free sql tips
agree to terms