Monday, 12 December 2011

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. 

3 comments:

  1. Harrah's Ak-Chin Casino & Hotel - Mapyro
    Harrah's Ak-Chin Casino & 동해 출장안마 Hotel Just 제주 출장안마 outside 문경 출장안마 of 남양주 출장안마 Caesars Palace, 정읍 출장마사지 this 4-star property sits on a 19-acre landscaped courtyard

    ReplyDelete
  2. GameArt Software Review - Is this an ok game? - Casino Whizz
    GameArt is a 바카라사이트 multi-platform casinosites company that has not only been producing games for video The studio is an obvious candidate for the slots and table games

    ReplyDelete