MySQL - regexp_replace() Function

MySQL: The REGEXP_REPLACE() Function

Regular expressions provide a powerful mechanism in MySQL for advanced search and manipulation operations. Beyond just filtering records, you can use them to find and replace specific patterns within strings, a common task in data cleaning and transformation.

Imagine discovering a recurring typo or needing to standardize a data format across thousands of records in your database. Manually correcting each entry is impractical and error-prone. The REGEXP_REPLACE() function is the ideal tool for this, allowing you to apply complex replacement logic efficiently and consistently.

Understanding the REGEXP_REPLACE() Function

The REGEXP_REPLACE() function, introduced in MySQL 8.0, searches a string for occurrences matching a regular expression pattern and replaces them with a new string. If a match is found, it returns the modified string; otherwise, it returns the original string. If any of the expression, pattern, or replacement arguments are NULL, the function returns NULL.

Syntax

The general syntax for the REGEXP_REPLACE() function is:

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

Parameters

The function accepts the following parameters:

• expr: The source string to be searched.
• pattern: The regular expression pattern to search for.
• repl: The string that will replace matching substrings.

It also accepts these optional arguments:

• pos: The position in expr to start the search. The default is 1.
• occurrence: Specifies which occurrence of a match to replace. If 0 (the default), all occurrences are replaced. If a positive integer N, the Nth occurrence is replaced.
• match_type: A string of characters that modifies the matching behavior. Common options include ‘i’ for case-insensitive matching, ‘c’ for case-sensitive matching (default), ‘m’ for multiline mode, and ‘u’ for Unix-only line endings.

Practical Examples

Example 1: Basic Replacement

In this query, we replace the word ‘temporary’ with ‘permanent’.

SELECT REGEXP_REPLACE('This is a temporary solution.', 'temporary', 'permanent') AS result;

The pattern is found, and the replacement is made:

result
This is a permanent solution.

Example 2: No Match Found

If the pattern is not found, the original string is returned unchanged.

SELECT REGEXP_REPLACE('This is a final solution.', 'temporary', 'permanent') AS result;

Output:

result
This is a final solution.

Example 3: Using Optional Parameters

This example demonstrates case-insensitive matching (‘i’), starting from position 5, and replacing only the first occurrence (1).

SELECT REGEXP_REPLACE('A TALL building on a Tall hill.', 'tall', 'short', 5, 1, 'i') AS result;

The search starts after ‘A TA’, finds ‘Tall’ (case-insensitively), and replaces it.

result
A TALL building on a short hill.

Example 4: Advanced Pattern Matching

The | (OR) operator allows you to match multiple alternatives. Here, we replace both ‘cat’ and ‘dog’ with ‘pet’.

SELECT REGEXP_REPLACE('I have a cat and a dog.', 'cat|dog', 'pet') AS result;

Output:

result
I have a pet and a pet.

Example 5: Using Capture Groups for Data Reformatting

Capture groups () let you reference parts of the matched string in your replacement string using $n or \n. This is extremely useful for reformatting data, like changing a date format from ‘YYYY-MM-DD’ to ‘MM/DD/YYYY’.

SELECT REGEXP_REPLACE('Date: 2024-07-26', '([0-9]{4})-([0-9]{2})-([0-9]{2})', '$2/$3/$1') AS formatted_date;

Output:

formatted_date
Date: 07/26/2024

Working with Table Data

Let’s apply REGEXP_REPLACE() to a table. First, create and populate a sample CUSTOMERS table.

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

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Alia', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

Example 6: Standardizing Phone Numbers in a Column

Imagine an employees table where phone numbers are stored inconsistently. We can standardize them to the (XXX) XXX-XXXX format.

-- First, add a phone number column and some sample data
ALTER TABLE CUSTOMERS ADD COLUMN PHONE VARCHAR(20);
UPDATE CUSTOMERS SET PHONE = '123-456-7890' WHERE ID = 1;
UPDATE CUSTOMERS SET PHONE = '123.456.7891' WHERE ID = 2;
UPDATE CUSTOMERS SET PHONE = '1234567892' WHERE ID = 3;

-- Now, run the update query to standardize the format
UPDATE CUSTOMERS
SET PHONE = REGEXP_REPLACE(PHONE,
    '([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})',
    '($1) $2-$3'
)
WHERE PHONE IS NOT NULL;

After running the update, you can verify the changes:

SELECT ID, NAME, PHONE FROM CUSTOMERS WHERE PHONE IS NOT NULL;

ID	NAME	PHONE
1	Ramesh	(123) 456-7890
2	Khilan	(123) 456-7891
3	Alia	(123) 456-7892

Using REGEXP_REPLACE() in Client Applications

You can execute queries with REGEXP_REPLACE() from any programming language. Below are modern examples for Python, Node.js, Java, and PHP, focusing on best practices like security and resource management.

Python Example (with mysql-connector-python)

This example uses a try...except...finally block for robust connection handling and parameterized queries to prevent SQL injection.

import mysql.connector
from mysql.connector import errorcode

# Best practice: Use environment variables or a config file for credentials
config = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'TUTORIALS'
}

sql_query = """
SELECT NAME,
       REGEXP_REPLACE(ADDRESS, 'bad$', 'BAD') AS modified_address
  FROM CUSTOMERS
 WHERE ADDRESS LIKE '%bad';
"""

connection = None
try:
    connection = mysql.connector.connect(**config)
    cursor = connection.cursor()
    print("Querying for address modifications...")
    cursor.execute(sql_query)

    for (name, modified_address) in cursor:
        print(f"- {name}'s modified address: {modified_address}")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Authentication error: check username/password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if connection and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed.")

# Expected Output:
# Querying for address modifications...
# - Ramesh's modified address: AhmedaBAD
# - Komal's modified address: HyderaBAD
# MySQL connection is closed.

Node.js Example (with mysql2/promise and async/await)

Modern JavaScript uses async/await for cleaner asynchronous code. The mysql2/promise library supports this syntax out of the box.

const mysql = require('mysql2/promise');

// Best practice: Use environment variables for configuration
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS'
};

async function main() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        console.log('Successfully connected to the database.');

        const sql = "SELECT REGEXP_REPLACE('Humpty dumpty sat on a wall', 'wall', 'fence') AS result;";
        const [rows, fields] = await connection.execute(sql);

        console.log('Query Result:');
        console.log(rows[0].result);

    } catch (error) {
        console.error('Error executing query:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

main();

// Expected Output:
// Successfully connected to the database.
// Query Result:
// Humpty dumpty sat on a fence.
// Connection closed.

Java Example (with JDBC and try-with-resources)

The try-with-resources statement automatically closes resources like Connection, Statement, and ResultSet, preventing resource leaks.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class RegexReplaceExample {
    // Use constants for connection details
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String sql = "SELECT REGEXP_REPLACE('This is a test.', 'test', 'demonstration') AS result;";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("Successfully connected and executed query.");
            if (rs.next()) {
                String result = rs.getString("result");
                System.out.println("Result: " + result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// Expected Output:
// Successfully connected and executed query.
// Result: This is a demonstration.

PHP Example (with MySQLi and Prepared Statements)

This example uses the object-oriented style of MySQLi and prepared statements, which is the modern, secure way to interact with databases in PHP.

<?php
// Database credentials should be stored securely, not hardcoded
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
$dbname = 'TUTORIALS';

// Enable error reporting for mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
    echo "Connected successfully.\n";

    // Using a prepared statement, even for a SELECT without user input, is a good habit.
    $sql = "SELECT REGEXP_REPLACE(?, ?, ?) AS result";
    $stmt = $mysqli->prepare($sql);

    $original_string = 'eat sleep repeat';
    $pattern = 'eat';
    $replacement = 'code';

    $stmt->bind_param('sss', $original_string, $pattern, $replacement);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    echo "Result: " . $row['result'] . "\n";

    $stmt->close();
    $mysqli->close();

} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

// Expected Output:
// Connected successfully.
// Result: code sleep repeat