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 we would like to replace all the occurrences specific string. This could be any column of an existing table also.

string_to_replace is the string, of which, all the occurrences are to be replaced in the existing string.

replacement_string is the new string that we would like to be replaced in place of string_to_replace.

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

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 particular 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.