In SQL Server you can use the REPLACE() function to replace all occurrences of a string with another string.

The function accepts three arguments; the string that contains the string to be replaced, the string to be replaced, and the string to replace it.

Example

Here’s an example to demonstrate.

SELECT REPLACE('Big dog', 'dog', 'cat');

Result:

Big cat

Multiple Occurrences

As mentioned, the REPLACE() function replaces all occurrences. So if the string to be replaced appears multiple times, they will all be replaced.

SELECT REPLACE('Big dogs and little dogs', 'dog', 'cat');

Result:

Big cats and little cats

Be Careful

It pays to be mindful when replacing text. You could easily get unintended results if you’re not careful.

SELECT REPLACE('Very dogmatic', 'dog', 'cat');

Result:

Very catmatic

Replace Multiple Characters

If you find yourself in the situation where you’re nesting multiple REPLACE() functions to replace multiple characters in various places within the string, try using the TRANSLATE() function instead. The TRANSLATE() function works similar to having nested REPLACE() functions, but with a slight difference.