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.