Replacing text in SQL Server is easy. SQL Server has the REPLACE function to perform this task.
SQL Server REPLACE
The REPLACE function in SQL Server has 3 parameters.
- Text to search
- Text to find
- Text to replace with
SELECT REPLACE('Full text to search in', 'search', 'replace') AS ReplacedText
The output is: Full text to replace in
In the above example, we are searching “Text to search in” for the word “search” and we are replacing it with the word “replace”. Pretty simple, right? Now let’s take it a step further.
SQL Server Case Sensitive Replace
Above we went over how to do a case insensitive replace on a string. (The REPLACE function actually uses the default collation of the input text that it is searching). To turn it in to a SQL Server case sensitive replace, we just need to add one small thing to the end. We need to change the collation of the text we are searching. Learn more about text collation here.
SELECT REPLACE('Full text to search in' COLLATE SQL_Latin1_General_CP1_CS_AS, 'SEARCH', 'replace') AS ReplacedText
The output is: Full text to search in
This example shows how the “SEARCH” text was not found because we are changing the input text to search collation to SQL_Latin1_General_CP1_CS_AS (which is case sensitive). The input string has “search” in lower case and we were searching for an upper case “SEARCH”.
Reference: http://msdn.microsoft.com/en-us/library/ms186862(v=sql.120).aspx