Replace in SQL Server

The Replace function in SQL is used to replace all the occurrences of a specified string, in a given string. It's syntax is:

Replace(existing_string, string_to_Replace, Replacement_string)

Here:

• existing_string is the string in which all the occurrences of a specific string are to be replaced. This could be any column of an existing table also.
• string_to_Replace is the string (in existing_string) of which all the occurrences are to be replaced.
• Replacement_string is the new string to replace the string to be replaced (string_to_Replace).

Let's use a simple example to discuss this concept now. See the commands below:

Replace in SQL Server

We have declared a simple string and applied the Replace function, to replace all the occurrences of "Test" with "updated". It will simply replace all the occurrences of "Test" in the existing string. Similarly we could have used any table column in the Select statement above.

Stuff vs Replace

It might sound quite similar to the stuff function but they are different. Stuff can be only used to Replace a specific string in our existing string. But, Replace will replace all the occurrences of a string, in the existing string. Also, stuff may or may not replace any characters in the string, but Replace will always replace the specified characters in the string. In other words, stuff can play the role of insertion and replacement in a string, but Replace will always play the role of replacement in a string.