MySQL REPLACE Function with Examples

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.


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment