MySQL REPLACE() – Replace All Instances of a Substring with another String

The MySQL REPLACE() function enables you to replace all occurrences of a substring with another string. It allows you to do things like, replace all occurrences of one word with another word, etc.

This article demonstrates its usage.

Syntax

Here’s how the syntax goes:

REPLACE(str,from_str,to_str)

Where str is the string that contains the substring/s. from_str is the substring you want to replace with another string. And to_str is the new string that will replace the old string.

Example

Here’s a basic example:

SELECT REPLACE('Cats and dogs and cats and rabbits', 'and', 'or') AS Result;

Result:

+---------------------------------+
| Result                          |
+---------------------------------+
| Cats or dogs or cats or rabbits |
+---------------------------------+

In this case, we simply swap the word and with the word or. Because there were three occurrences of that word, all three were replaced.

Case-Sensitve

It’s important to remember that the REPLACE() function is case-sensitive.

Example:

SELECT REPLACE('Cats and dogs and cats and rabbits', 'cat', 'flea') AS Result;

Result:

+-------------------------------------+
| Result                              |
+-------------------------------------+
| Cats and dogs and fleas and rabbits |
+-------------------------------------+

In this case, only one instance of cat was replaced, because only one instance had the right case. The first instance had an uppercase C so it didn’t match.

Remove a Substring

You can also remove a substring altogether, simply by replacing the substring with the empty string (''):

SELECT REPLACE('http://www.database.guide', 'www.', '') AS Result;

Result:

+-----------------------+
| Result                |
+-----------------------+
| http://database.guide |
+-----------------------+

A Safeguard

Another (perhaps safer) way of doing this is to include some of the surrounding text, then remove the unnecessary part:

SELECT REPLACE('http://www.database.guide', 'http://www.', 'http://') AS Result;

Result:

+-----------------------+
| Result                |
+-----------------------+
| http://database.guide |
+-----------------------+

This guards against inadvertently removing a substring that shouldn’t be removed. For example, if we have a URL like  http://bestwww.com, which would inadvertently turn into http://bestcom without the safeguard.

The same safeguard can be applied in cases where you’re replacing text (not just removing it). For example, this:

SELECT REPLACE('Land of cats and dogs and sand', ' and ', ' or ') AS Result;

Result:

+------------------------------+
| Result                       |
+------------------------------+
| Land of cats or dogs or sand |
+------------------------------+

In this case I added a space before and after the substring and its replacement text.

If I hadn’t done this, I would’ve ended up with this:

SELECT REPLACE('Land of cats and dogs and sand', 'and', 'or') AS Result;

Result:

+----------------------------+
| Result                     |
+----------------------------+
| Lor of cats or dogs or sor |
+----------------------------+