In SQL Server, you can use the T-SQL REPLACE() function to replace all instances of a given string with another string. For example, you can replace all occurrences of a certain word with another word.

Syntax

Here’s the official syntax:

REPLACE ( string_expression , string_pattern , string_replacement )

Where string_expression is the string that contains one or more instances of the string (or substring) to replace, string_pattern is the string to replace, and string_replacement is the string to replace it.

Example

Here’s an example to demonstrate:

SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some', 'no');

Result:

My apartment has no art hanging on the walls and no pot plants hanging from the ceiling.

So in this example we simply replace the word some with the word no.

Multiple Words

Of course, there’s no rule that says you can only replace a word with one word (and vice-versa). After all, we’re simply replacing a string with another string, regardless of whether that string contains words, letters, numbers, spaces, etc.

So we could easily have replaced that one word with two or more words:

SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some', 'lots of');

Result:

My apartment has lots of art hanging on the walls and lots of pot plants hanging from the ceiling.

So here we replace the word some with the words lots of.

Remove a Word

You can also remove a word (or substring) from the string. To do this, simply replace it with the empty string:

SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some', '');

Result:

My apartment has  art hanging on the walls and  pot plants hanging from the ceiling.

However, if you look closely, you’ll see that the new string contains double spaces where we removed the word. This is because the word that we removed had spaces to its left and right. We removed the word but we didn’t remove any spaces, therefore, two spaces remain.

We can fix this by including one of the spaces in the word to be removed:

SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'some ', '');

Result:

My apartment has art hanging on the walls and pot plants hanging from the ceiling.

Be Careful!

It’s very easy to make mistakes when using the REPLACE() function (or any find and replace functionality for that matter).

For example, this mistake:

SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', 'art', 'pictures');

Result:

My appicturesment has some pictures hanging on the walls and some pot plants hanging from the ceiling.

As you can see, we replaced the word art with pictures. However, in this case the word apartment was also affected – it has turned into apicturesment, which was not intended. This is because the word apartment contains the substring art.

You can usually safeguard against this by adding spaces around the search word, as well as the replacement word:

SELECT REPLACE('My apartment has some art hanging on the walls and some pot plants hanging from the ceiling.', ' art ', ' pictures ');

Result:

My apartment has some pictures hanging on the walls and some pot plants hanging from the ceiling.

This obviously assumes that you’re replacing the whole word. You’ll need to gauge each situation as it arises.

Collation/Case-Sensitivity

You can use the optional COLLATE clause to apply an explicit collation to the input. This can be handy for performing case-sensitive find/replace operations and the like.

Here’s an example that compares two collations.

Case-Insensitive

SELECT REPLACE('Cats, cats, and more cats!' COLLATE SQL_Latin1_General_CP1_CI_AS, 'cat', 'Dog');

Result:

Dogs, Dogs, and more Dogs!

In this example, the collation we specify includes _CI in its name, which means “Case Insensitive”.  This causes all occurrences to be replaced, despite the first occurrence having uppercase characters.

You’ll notice that this method doesn’t affect the case of the replaced string.

Case-Sensitive

SELECT REPLACE('Cats, cats, and more cats!' COLLATE SQL_Latin1_General_CP1_CS_AS, 'cat', 'Dog');

Result:

Cats, Dogs, and more Dogs!

In this example, the collation we specify includes _CS in its name, which means “Case Sensitive”.  This causes the first occurrence of Cat to be skipped, because its first character is an uppercase letter (which doesn’t match the case of the second argument).