MySQL - REGEXP_REPLACE() Function

Regular expressions in MySQL are used in search operations to not only filter records but also replace the pattern occurrences in a string.

Consider a scenario where you noticed a spelling error among the huge sets of data present in a MySQL database. Now, you are supposed to correct all occurrences of these errors in this database without disturbing the other data. This is where regular expressions are extremely advantageous.

You can use regular expressions to find the accurate occurrences of the same error and replace it with the right characters. This is done using the regexp_replace() function.

MySQL REGEXP_REPLACE() Function

The MySQL regexp_replace() function is used to find and replace occurrences of a string that match specific patterns. If there's a match, it replaces the string with another. If there's no match, it returns the original string. If the string or pattern is NULL, it returns NULL. You can use a regular expression or a simple string as the pattern in this function.

Syntax

Following is the syntax of the MySQL regexp_replace() function −

REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])

Parameters

The regexp_replace() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

• repl: The replacement string

This method also accepts following optional arguments −

• pos − Starting position of the search

• occurrence − Which occurrence of a match to replace. If omitted, the default is 0 so it replaces all occurrences.

• match_type − A string that specifies how to perform matching.

Example

In the following query, we are performing a search operation on a simple string using the MySQL REGEXP_REPLACE() function −

SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welll')
AS RESULT;

As we can observe the output below, the string 'Welcome' is found and replaced with 'Welll' −

But if the pattern is not found in the string, the original string is displayed by the function. Look at the following query −

SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'H', 'Hi') AS RESULT;

On executing the given query, the output is displayed as follows −

Example

Let us also try to pass optional arguments to this function as case-insensitive matching(i). Here, the search starts from the 10th position in the given string; and as we are passing the occurrence value as 1, only the first occurrence of the letter 't' after 10th position will be replaced irrespective of its case −

SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 't', 'x', 10, 1, 'i') AS RESULT;

Output

The output for the program above is produced as given below −

Example

The following query replaces all the occurrences of the string "is" in the given text −

SELECT REGEXP_REPLACE('This is a sample string', 'is', '@@@@')
As Result;

Output

On executing the given query, the output is displayed as follows −

Example

The following query replaces only the first occurrence of the string "This" in the given text with "That" −

SELECT REGEXP_REPLACE('This is a test and This is another test', '^This', 'That')
As Result;

Output

The output for the query above is produced as given below −

Example

Here, the below query replace the words 'wall' or 'floor' with the word 'bed' in the given string using the MySQL REGEXP_REPLACE() function −

SELECT REGEXP_REPLACE ('Humpty dumpty sat on a wall and slept on the floor', 'wall|floor', 'bed') As Result;

Output

On executing the given program, the output is displayed as follows −

Example

The following query replaces the first occurrence of the string "eat" with the string "drink" in the provided input string.

In the query, the fourth parameter "1" specifies the position to start the search and the fifth parameter "1" is the number of replacements to be made. Therefore, only the first occurrence of "eat" is replaced with "drink".

SELECT REGEXP_REPLACE('eat sleep repeat and eat', 'eat', 'drink', 1, 1)
As Result;

Output

Following is the output −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing NULL to the string parameter.

SELECT REGEXP_REPLACE(NULL, 'value', 'test') As Result;

Following is the output −

If we pass NULL to the pattern parameter, it returns NULL as output.

SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, 'sample')
As Result;

The output for the query above is produced as given below −

If you pass empty string as the replacement string, this function returns NULL.

SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, '')
As Result;

On executing the given query, the output is displayed as follows −

Example

In another example, let us try to perform a search operation on a database table named CUSTOMERS using the REGEXP_REPLACE() function. First of all, let us create the table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the following SELECT statement to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

The following query uses the REGEXP_REPLACE() function to update the NAME column in the person_tbl table. It looks for names that start with the letter 'A' and replaces that 'A' with 'An'.

SELECT REGEXP_REPLACE(NAME, '^A', 'An') AS Result FROM CUSTOMERS;

On executing the given query, the output is displayed as follows −

But if the pattern is not found in any record of the table, the original values of the table are displayed by the function. Look at the following query −

SELECT REGEXP_REPLACE(ADDRESS, '^Z', 'P') AS RESULT FROM CUSTOMERS;

There is no record in ADDRESS column that starts with letter 'Z'. So, it returned the original records as output −

The following query is using the REGEXP_REPLACE function to replace the second occurrence of the letter 'r' with 'R' in the ADDRESS column of the CUSTOMERS table −

SELECT REGEXP_REPLACE(ADDRESS, 'r', 'R', 2, 0, 'c') 
AS RESULT FROM CUSTOMERS;

As we can see in the output, the records 'Hyderabad' and 'Indore' has letter 'r' in it. And they are replaced by 'R' −

REGEXP_REPLACE() Funcion Using a Client Program

We can also perform the MySQL REGEXP_REPLACE function using the client programs to find and replace occurrences of a string that match specific patterns.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
$mysqli->query($sql);

sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
con.query(sql);

String sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
statement.executeQuery(sql);

sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')" 
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row['RESULT']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: Welcom To Tutorialspoint!

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});     

Select query executed successfully..!
Table records:
[ { RESULT: 'Welcome To Tutorialspoint!' } ]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_replace {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Result: Welcom To Tutorialspoint!

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_replace_query = f"SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT;"
cursorObj.execute(regexp_replace_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_REPLACE() Function:")
for row in results:
    result = row[0]
    print(f"The modified string is: '{result}'")
cursorObj.close()
connection.close()    

Result of REGEXP_REPLACE() Function:
The modified string is: 'Welcom To Tutorialspoint!'