MySQL REGEXP_REPLACE() Function

Summary: in this tutorial, you will learn how to use the MySQL REGEXP_REPLACE() function to replace matches with a new substring.

Introduction to the MySQL REGEXP_REPLACE function

The REGEXP_REPLACE() function replaces the matches of a regular expression with a new substring.

Here’s the syntax of the REGEXP_REPLACE() function:

REGEXP_REPLACE(
   string, 
   pattern, 
   replacement, 
   position,  
   occurrence, 
   match_type
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

string: The input string where you want to find the matches.
pattern: The regular expression pattern that you want to match.
replacement: This is the new substring that the function will replace the matches.
position: The position in the string at which the function starts searching. The default value is 1 which instructs the function to search from the beginning of the string.
occurrence: This determines which occurrence of a match to replace. The default value of occurrence is zero (0), which replaces all occurrences.
match_type: This is a string that contains one or more options that instruct how the function should perform matching.

The function REGEXP_REPLACE() returns the result string where occurrences of the matches are replaced with the new substring.

If string, pattern, or replacement is NULL, the function REGEXP_REPLACE() will return NULL.

MySQL REGEXP_REPLACE function examples

Let’s take some examples of using the REGEXP_REPLACE() function.

1) Simple MySQL REGEXP_REPLACE() function example

The following example shows how to replace all non-digit characters in a phone number with an empty string:

SELECT 
  REGEXP_REPLACE('(212)-456-7890', '\\D', '') phone_number;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------+
| phone_number |
+--------------+
| 2124567890   |
+--------------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the pattern \\D matches non-digit characters in the input string that includes the characters (, ), and -. It replaces these characters with an empty string and returns the phone number with only digits.

2) position example

The following starts searching at the position 6 and replace the matches with an empty string:

SELECT 
  REGEXP_REPLACE('(212)-456-7890', '\\D', '', 6) phone_number;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------+
| phone_number |
+--------------+
| (212)4567890 |
+--------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the REGEXP_REPLACE() function replaces the first and second characters – with an empty string.

3) occurrence example

The following example uses the REGEXP_REPLACE() function to replace the first occurrence of a non-digit character with a space:

SELECT 
 REGEXP_REPLACE('+1(484)-476-0002', '\\D', '', 1, 1) phone_number;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| phone_number    |
+-----------------+
| 1(484)-476-0002 |
+-----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we use the occurrence argument with the value 1 to instruct the function REGEXP_REPLACE to replace only the first occurrence.

4) Using REGEXP_REPLACE function with table data

First, create a new table called contacts with three fields: id, name, and phone:

CREATE TABLE contacts(
   id INT AUTO_INCREMENT,
   name VARCHAR(255) NOT NULL,
   phone VARCHAR(25) NOT NULL,
   PRIMARY KEY(id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert five rows into the contacts table:

INSERT INTO contacts (name, phone)
VALUES
    ('John Doe', '+1(484)-476-0002'),
    ('Jane Smith', '+1(555)-987-6543'),
    ('Bob Johnson', '+1(555)-555-5555'),
    ('Alice Brown', '+1(555)-111-2222'),
    ('Eve White', '+1(555)-999-8888');Code language: SQL (Structured Query Language) (sql)

Third, use the REGEXP_REPLACE() function to replace non-digit characters in the phone number with an empty string:

UPDATE 
  contacts 
SET 
  phone = REGEXP_REPLACE(phone, '\\D', '');Code language: SQL (Structured Query Language) (sql)

Output:

Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0Code language: SQL (Structured Query Language) (sql)

The UPDATE statement modified all five rows in the contacts table.

Finally, query data from the contacts table:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------+-------------+
| id | name        | phone       |
+----+-------------+-------------+
|  1 | John Doe    | 14844760002 |
|  2 | Jane Smith  | 15559876543 |
|  3 | Bob Johnson | 15555555555 |
|  4 | Alice Brown | 15551112222 |
|  5 | Eve White   | 15559998888 |
+----+-------------+-------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the REGEXP_REPLACE() function to replace matches with a new substring using regular expressions.

Was this tutorial helpful?