MySQL - regexp_substr() Function

MySQL REGEXP_SUBSTR() Function

Introduction to Regular Expressions in MySQL

Regular expressions (regex) are a powerful tool for advanced pattern matching in strings. While simple LIKE clauses are useful for basic wildcard searches, regular expressions allow you to define complex search patterns. In MySQL 8.0 and later, a set of robust, ICU-compliant regular expression functions were introduced, including REGEXP_SUBSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_INSTR().

REGEXP_SUBSTR() is specifically used to extract a substring that matches a given regular expression pattern. This is incredibly useful for parsing and extracting specific pieces of information from unstructured or semi-structured text data stored in your database.

The REGEXP_SUBSTR() Function

The REGEXP_SUBSTR() function searches a string for a substring that matches a regular expression pattern and returns that substring. If no match is found, it returns NULL. This is different from REGEXP_LIKE(), which only returns 1 (true) or 0 (false).

Syntax

REGEXP_SUBSTR(expression, pattern[, position[, occurrence[, match_type]]])

Parameters

This function accepts the following arguments:

• expression: The input string to search within.
• pattern: The regular expression pattern to match.
• position (Optional): The starting position for the search within the string. Defaults to 1.
• occurrence (Optional): Specifies which occurrence of the pattern to find. Defaults to 1 (the first occurrence).
• match_type (Optional): A string specifying match options. For example: ‘c’ for case-sensitive matching, ‘i’ for case-insensitive, ‘m’ for multi-line mode. The default is case-sensitive.

Basic and Advanced Examples

Example 1: Simple Pattern Matching

Let’s find the first word in a sentence. The pattern \w+ matches one or more word characters (letters, numbers, underscore).

SELECT REGEXP_SUBSTR('Modern SQL is powerful!', '\\w+') AS first_word;

Note: In SQL strings, the backslash \ is an escape character, so you often need to double it \\ to represent a literal backslash in the regex pattern.

first_word
Modern

Example 2: No Match Found

If the pattern does not exist in the string, the function returns NULL.

SELECT REGEXP_SUBSTR('Modern SQL is powerful!', 'MySQL') AS result;

result
NULL

Example 3: Using Position and Occurrence

Let’s find the second word (occurrence = 2) in the same sentence.

SELECT REGEXP_SUBSTR('Modern SQL is powerful!', '\\w+', 1, 2) AS second_word;

second_word
SQL

Example 4: Practical Use Case - Extracting a Domain Name

A common task is to extract the domain name from an email address. The pattern '@(.*)$' captures everything after the @ symbol.

SELECT REGEXP_SUBSTR('developer@example.co.uk', '@(.*)$') AS domain_part;

This returns @example.co.uk. To get just the domain without the @, we can use REGEXP_REPLACE or combine REGEXP_SUBSTR with SUBSTRING.

SELECT SUBSTRING(REGEXP_SUBSTR('developer@example.co.uk', '@(.*)$'), 2) AS domain_name;

domain_name
example.co.uk

Usage on Table Data

Let’s create a products table with a version string and extract the major version number.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    version_code VARCHAR(50)
);

INSERT INTO products (product_name, version_code) VALUES
    ('Data Analyzer', 'v2.1.5-beta'),
    ('Image Editor', 'v3.0.1-stable'),
    ('Text Processor', 'Legacy v1.9'),
    ('Firewall', '4.0');

We want to extract the first sequence of digits, which represents the major version number. The pattern [0-9]+ or \\d+ matches one or more digits.

SELECT
    product_name,
    version_code,
    REGEXP_SUBSTR(version_code, '\\d+') AS major_version
FROM products;

product_name	version_code	major_version
Data Analyzer	v2.1.5-beta	2
Image Editor	v3.0.1-stable	3
Text Processor	Legacy v1.9	1
Firewall	4.0	4

Using REGEXP_SUBSTR() in Client Applications

Calling REGEXP_SUBSTR() from a client application is straightforward. Remember to properly handle potential NULL return values.

PHP (PDO)
Node.js (mysql2/promise)
Java (JDBC)
Python (mysql-connector-python)




```php
<?php
// Assuming $pdo is a connected PDO object from the previous tutorial

$sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products";

$stmt = $pdo->query($sql);

echo "Product Major Versions:\n";
while ($row = $stmt->fetch()) {
    $majorVersion = $row['major_version'] ?? 'N/A'; // Handle NULL
    echo "- {$row['product_name']} ({$row['version_code']}): Major version is {$majorVersion}\n";
}
?>

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

main.js

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

async function getMajorVersions() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* connection config */ });

        const sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products";
        const [rows] = await connection.execute(sql);

        console.log('Product Major Versions:');
        for (const row of rows) {
            const majorVersion = row.major_version || 'N/A'; // Handle null
            console.log(`- ${row.product_name} (${row.version_code}): Major version is ${majorVersion}`);
        }
    } catch (error) {
        console.error('Error fetching product versions:', error);
    } finally {
        if (connection) await connection.end();
    }
}

getMajorVersions();

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

ProductVersionExtractor.java

import java.sql.*;

public class ProductVersionExtractor {
    // Assuming DB_URL, USER, PASS are defined
    public static void main(String[] args) {
        String sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products";

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

            System.out.println("Product Major Versions:");
            while (rs.next()) {
                String productName = rs.getString("product_name");
                String versionCode = rs.getString("version_code");
                String majorVersion = rs.getString("major_version");
                if (rs.wasNull()) {
                    majorVersion = "N/A"; // Handle NULL
                }
                System.out.printf("- %s (%s): Major version is %s%n", productName, versionCode, majorVersion);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

version_extractor.py

import mysql.connector

def get_major_versions():
    try:
        conn = mysql.connector.connect(/* connection config */)
        cursor = conn.cursor(dictionary=True) # Fetch rows as dictionaries

        sql = "SELECT product_name, version_code, REGEXP_SUBSTR(version_code, '\\d+') AS major_version FROM products"
        cursor.execute(sql)

        print('Product Major Versions:')
        for row in cursor.fetchall():
            major_version = row.get('major_version') or 'N/A' # Handle None
            print(f"- {row['product_name']} ({row['version_code']}): Major version is {major_version}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    get_major_versions()

Output: Product Major Versions: - Data Analyzer (v2.1.5-beta): Major version is 2 - Image Editor (v3.0.1-stable): Major version is 3 ...

## Best Practices

- **Performance:** Regular expression operations can be CPU-intensive. Avoid using them in `WHERE` clauses on large, unindexed columns if possible. If you frequently search by a pattern, consider extracting the relevant data into a separate, indexed column during data ingestion.
- **Complexity:** Keep patterns as simple as possible. Complex regex can be difficult to read, debug, and maintain.
- **Escaping:** Remember to escape backslashes (`\`) in your SQL string literals (e.g., `\d` becomes `\\d`).
- **Data Normalization:** While regex is powerful for parsing existing data, the best long-term solution is often to normalize your data schema. For instance, storing major, minor, and patch versions in separate `INT` columns is far more efficient than parsing a version string every time.