Standardize Text with Multi Search and Replace Function in SQL Server
By: Greg Robidoux | Comments (3) | Related: More > 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:
About the author
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.
View all my tips