Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Standardize Text with Multi Search and Replace Function in SQL Server


By:   |   Last Updated: 2010-08-25   |   Comments (3)   |   Related Tips: 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:


Last Updated: 2010-08-25


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Nice one,  dbo.fnReplaceWords_2 did just what i needed

 


Saturday, May 09, 2015 - 6:59:09 AM - Greg Robidoux Back To Top

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 08, 2015 - 6:40:07 PM - paco Back To Top

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


Learn more about SQL Server tools