Have you ever had the need to replace multiple words in a data column within SQL Server with a new word or a new phrase for an entire table? A good example would be to update a product catalog with new words either for standardization or just to reflect a new name for the product. A simple technique would be to use the REPLACE command as outlined in this prior tip. This is great if you only need to update a few words, but what if you need to scan the entire product catalog and make several changes across the board, sometimes multiple changes for one data value?
By using a combination of the text manipulation commands from this prior tip and also using a cursor we can cursor through the data one time and update all words in the data column based on values that exist in a synonyms table to reflect the new way we want the data to be stored. The downside is that we will need to use a cursor, but the upside is that we can scan through all of our data one time instead of having to do multiple table scans to find the word we are looking for and then doing the update.
Here is the script that will scan through an entire table using a cursor, for each row of data the text is broken down word by word and then looked up in a synonyms table to see if there is a standardized way of storing this word. If found the new value is brought in to create an updated version of our data based on the lookup.
Here is the code that does the conversion.
So to show how this can be used we need to create a new table called synonyms. We will also create a unique index on the word column. This will allow us to use the index for the data lookup and also make sure that only one row is returned.
To run the example create this table in the Northwind database or create this in your own database to use with your data.
To test against the Product table in the Northwind database we need to create some data in the synonyms table. Here is a sample of what we will create.
You can either create the data manually or run this script to populate the table.
So after creating the table, loading the data and running the script above these are the results we get when using the data in the Northwind database. As you can see we have updated the data based on the our word replacements. Also, you will notice that we have updated "Sir Rodney's Marmalade" to "King Rodney's Jam". The process did the replacement for both "Sir" and for "Marmalade".
|Sir Rodney's Marmalade||King Rodney's Jam|
|Sir Rodney's Scones||King Rodney's Scones|
|Jack's New England Clam Chowder||Jack's New England Clam Chowda|
|Louisiana Fiery Hot Pepper Sauce||Louisiana Fiery Wicked Hot Pepper Sauce|
|Louisiana Hot Spiced Okra||Louisiana Wicked Hot Spiced Okra|
- Download all the scripts here
- Modify this code to work with your data
- Make some changes so the final result updates the data instead of just displaying the before and after versions
- Although the text manipulation commands are limited, get creative with what is available to solve your data problem
Last Update: 9/12/2006
About the author
View all my tips