A few weeks ago I searched many SQL Server websites for T-SQL code that modified words. Specifically, I needed to replace a word in a table’s text column. I found many code samples, but in all cases the code modified a string rather than a word. As a result, if the word I wanted to modify was part of another word, the modification also changed this word. For example, changing the word Bikes to Trucks using these code samples also modified SuperBikes to SuperTrucks. In many cases, the string-modification code also didn’t catch the word I was searching for if the word was at the beginning or end of the text column.
Because I didn’t find what I was looking for, I wrote a user-defined function (UDF) named cm_ ModifyWord. You can use this UDF to modify a word (and not a string) in any position within a text column (i.e., beginning, middle, or end). The UDF even works when words are delimited by punctuation characters such as a period (.), comma (,), semicolon (;), colon (:), or exclamation point (!).
You can download cm_ModifyWord by clicking on the 100217.zip hotlink at the top of this page. This UDF works on SQL Server 2005.
The code in Listing 2 demonstrates how to use the cm_ModifyWord function. This code replaces the word Bikes with Trucks in the Name column of the AdventureWorks database’s Sales.Store table. If you run this code, you’ll find that it doesn’t change Super- Bikes to SuperTrucks.