Scenario: Within your database you have a table, say NewsStories for example. This table has a varchar column containing significant amounts of text per row, say ArticleBody. You notice that in every article written this month, a particularly reporter's name has been spelled wrong. The articles are live, and this needs to be fixed... fast.
So, you need to replace a particular bit of text (the name), in a particular subset of data (the articles from this month) without disturbing anything else. Luckily, the Replace function makes it easy!
T-SQL Replace takes 3 arguments. I like to think of them as the 3 Ws - Where, What, With. That is:
Replace( Where, What, With )
Where: the full string we're going to be working on. It could be a literal ('some text') or a column name (ArticleBody), or any other valid expression that produces a char string.
What: the sub-string we're looking to replace. Again this can be any valid character expression, but it can't be an empty string.
With: The string we want to put there instead.
The Replace function returns a new string with the replacement completed.
So, how do tackle the replacement within a particular column. Using our example scenario above:
That's it, all done! Remember of course, like any update, this is a destructive change so be damn sure you triple-check what you're replacing. It's a simple function but extremely useful on many occasions.
