How to replace substrings with MySQL REPLACE

You can use the MySQL REPLACE command to fix errors or replace obsolete information in a string. Make sure to check upper and lower case.

The function of MySQL REPLACE can be summarized in one sentence: to replace all occurrences of a substring with another substring. While true in principle, it’s worth first looking at the structure of MySQL. The database management system uses tables to store data and make it available when needed. This data is stored in the form of strings, which are enclosed in single or double quotes. But typos can creep in, or stored information can become obsolete. This is where MySQL REPLACE comes into play

Instead of tediously searching for an occurrence in a substring and running the risk of missing a variant, MySQL REPLACE does the job for you. The command searches for all places where the stored substring occurs and replaces it with new information. This way, you can replace an outdated URL, fix numbers or typos, or update other changed values in your table. The result is an optimized, updated collection of data. Plus, MySQL REPLACE is simple to use with just a few pitfalls.

When you learn MySQL, you’ll come across commands like MySQL CONCAT, MySQL DELETE, MySQL DATE, or MySQL DATEDIFF. Compared to these functions, MySQL REPLACE is a bit more demanding, since the command consists of three different parameters separated by commas. However, it makes sense when you look at the syntax:

The “string” parameter defines the source string in which the exchange is to be performed. “old_substring” refers to the substring that you want to find and exchange. The last parameter MySQL REPLACE needs is the substring that the old one should be replaced. The new substring replaces the old one everywhere in the given string.

To understand how MySQL REPLACE works, we’ll use a simple example. Let’s take a series of words and simply change their meaning with the command. It looks this:

We’re instructing the system to replace the letter O with an A in the string “DOG DOVE DOOR”. This change is made wherever the “O” occurs in the current version. The output is:

It’s important to remember that MySQL REPLACE is case sensitive. In our example, above this doesn’t matter yet, but in the following example you can see why it can make a difference.

The output for this example looks like this:

If you forget about the case when writing the command, it will mean that MySQL REPLACE won’t achieve the desired result. If you were to insert a lowercase “r” as the old substring in the above example, the replacement wouldn’t be successful since the letter doesn’t occur in the string in this notation.

MySQL REPLACE doesn’t only work with letters but also with numbers. How it works is identical though. In the following examples there is an obvious error, which we can easily fix with MySQL REPLACE:

The output looks better like this:

While the examples above give you an idea of how you can use MySQL REPLACE, the following example shows you how the command can help clean up your data. Here, let’s imagine that you have discovered a typo in the “City” column of a customer list. Instead of “Berlin” it says “Belrin” at least once. Instead of deleting all entries, you can replace the incorrect city with the correct spelling:

Now the typo has been fixed.