Using T-SQL REPLACE

Replacing Specific Text in Microsoft SQL (T-SQL)
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.