How to Use the MySQL REPLACE Function
In this tutorial, you'll learn how to use the MySQL REPLACE function. REPLACE is a function to replace all occurrences of an old substring within a given string with a new substring.
MySQL REPLACE() Syntax
The syntax of the REPLACE() function is as follows:
REPLACE(string, old_substring, new_substring)
Remark
- String: The string you want to replace
- Old_substring: The substring to be replaced
- New_substring: The new substring to replace the old_substring
- REPLACE is case-sensitive. So, for example, Best and best are not considered to be the same, and Best can't be replaced with best.
REPLACE() Examples
Example 1: Replace a Single Word
The following statement replaces "tutorial" with "guide":
SELECT REPLACE("Are you looking for the best MySQL tutorials?", "tutorial", "guide");
Result:
Are you looking for the best MySQL guides?.
Example 2: Replace all Occurrences of a Word
The following SQL statement replaces all occurrences of "tutorial" with "guide":
SELECT REPLACE("There are a lot of SQL tutorials on the web, but this site also provides the best tutorials.", "tutorial", "guide");
Result:
There are a lot of SQL guides on the web, but this site also provides the best guides.
Example 3: REPLACE is Case-Sensitive
The following statement replaces "tutorial" with "guide":
SELECT REPLACE("Tutorial", "tutorial", "guide");
There is no "tutorial" in the specified string, and REPLACE is case-sensitive, so the returned result is the same as the original string.
Example 4: Replace Blank Spaces
In this example, I show you how to write a statement to remove all leading and trailing spaces and replaces two blank spaces between words with a single space.
Let's take a look at an example:
SELECT REPLACE (TRIM(" This site provides a lot of tutorials "), " ", " ");
First, we use TRIM() to remove all leading and trailing spaces from the string, and then we get the result as follows:
This site provides a lot of tutorials
Next, we replace two blank spaces between words with a single space,
and then we get the result as follows:
This site provides a lot of tutorials
If there are more than two spaces between words, you can not do this way. In such a case, you should use the REGEXT function instead.
In this tutorial, you've learned how to use the REPLACE function. We use REPLACE to replace all occurrences of an old substring within a given string with a new substring. REPLACE is case-sensitive, so the uppercase and lowercase of the same words are treated differently.