MySQL - regexp_like() Function

MySQL: The REGEXP_LIKE() Function

In MySQL, pattern matching is essential for filtering data. While the LIKE operator handles simple wildcard searches, REGEXP_LIKE() provides the full power of regular expressions to perform complex and precise pattern validation.

Unlike functions that return the position (REGEXP_INSTR()) or the matched string (REGEXP_SUBSTR()), REGEXP_LIKE() serves as a boolean check. It simply tells you if a pattern exists in a string, making it perfect for use in WHERE clauses to filter records based on sophisticated rules.

Understanding the REGEXP_LIKE() Function

The REGEXP_LIKE() function, available in MySQL 8.0 and later, evaluates a string against a regular expression pattern. It returns 1 (true) if the pattern is found, 0 (false) if it is not, and NULL if either the expression or pattern is NULL. It is functionally equivalent to the RLIKE or REGEXP operators but offers a clearer, more function-oriented syntax.

Syntax

The syntax for the REGEXP_LIKE() function is:

REGEXP_LIKE(expr, pattern[, match_type])

Parameters

• expr: The source string to be checked.
• pattern: The regular expression pattern to search for.
• match_type: (Optional) A string that modifies matching behavior. Common options are ‘i’ for case-insensitive, ‘c’ for case-sensitive (default), ‘m’ for multiline mode, and ‘n’ to allow ’.’ to match line terminators.

Practical Examples

Example 1: Basic Pattern Check

This query checks if the string contains the word ‘Tutorials’.

SELECT REGEXP_LIKE('Welcome to TutorialsPoint!', 'Tutorials') AS result;

The pattern is found, so the function returns 1.

result
1

Example 2: Case-Insensitive Matching

Using the ‘i’ match type, we can find ‘tutorials’ regardless of its case.

SELECT REGEXP_LIKE('Welcome to TutorialsPoint!', 'tutorials', 'i') AS result;

Even though the case differs, the match is successful.

result
1

Example 3: Handling NULL values

If either the expression or the pattern is NULL, the result is NULL.

SELECT REGEXP_LIKE('Some text', NULL) AS result_null_pattern;
SELECT REGEXP_LIKE(NULL, 'some pattern') AS result_null_expr;

Both queries will return NULL.

result_null_pattern
NULL

Filtering Table Data with REGEXP_LIKE()

The true power of REGEXP_LIKE() is revealed when used in a WHERE clause. Let’s set up a sample table.

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

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);

Example 4: Finding Names Starting with ‘K’

The ^ anchor in regex matches the beginning of a string. This query finds all customers whose name starts with ‘K’.

SELECT NAME, ADDRESS FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, '^K');

This returns all names that satisfy the condition.

NAME	ADDRESS
Khilan	Delhi
Kaushik	Kota
Komal	Hyderabad

Example 5: Finding Addresses Containing ‘r’ (Case-Insensitive)

This query finds all customers where the address contains the letter ‘r’ or ‘R’.

SELECT NAME, ADDRESS FROM CUSTOMERS WHERE REGEXP_LIKE(ADDRESS, 'r', 'i');

The result includes addresses with ‘r’.

NAME	ADDRESS
Ramesh	Ahmedabad
Komal	Hyderabad
Muffy	Indore

Best Practice: LIKE vs. REGEXP_LIKE

For simple prefix, suffix, or substring searches, the LIKE operator is significantly faster and should be preferred. REGEXP_LIKE is more resource-intensive and should be reserved for patterns that cannot be expressed with LIKE’s % and _ wildcards.

• Use LIKE for: WHERE NAME LIKE 'K%' (faster)
• Use REGEXP_LIKE for: WHERE REGEXP_LIKE(NAME, '^K') (more powerful, but slower for this simple case)
• Use REGEXP_LIKE for complex patterns: WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') (cannot be done with LIKE)

Using REGEXP_LIKE() in Client Applications

You can easily use REGEXP_LIKE in your application code to perform powerful server-side data validation and filtering. Here are modern examples.

Python Example (with mysql-connector-python)

import mysql.connector

# Best practice: Use a configuration function or file for credentials
def get_db_connection():
    return mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password',
        database='TUTORIALS'
    )

# Find customers whose names end with 'sh'
sql_query = "SELECT NAME FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, %s);"
pattern = 'sh$'

try:
    with get_db_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute(sql_query, (pattern,))
            results = cursor.fetchall()
            print(f"Found {cursor.rowcount} customers whose name ends with 'sh':")
            for row in results:
                print(f"- {row[0]}")
except mysql.connector.Error as err:
    print(f"Database Error: {err}")

# Expected Output:
# Found 2 customers whose name ends with 'sh':
# - Ramesh
# - Kaushik

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

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

const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'TUTORIALS'
};

async function findCustomersByPattern(pattern) {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const sql = "SELECT NAME FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, ?);";
        const [rows] = await connection.execute(sql, [pattern]);

        console.log(`Customers with names containing 'al':`);
        if (rows.length > 0) {
            rows.forEach(row => console.log(`- ${row.NAME}`));
        } else {
            console.log('No matching customers found.');
        }
    } catch (error) {
        console.error('Failed to query database:', error);
    } finally {
        if (connection) await connection.end();
    }
}

findCustomersByPattern('al');

// Expected Output:
// Customers with names containing 'al':
// - Khilan
// - Chaitali
// - Komal

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

import java.sql.*;

public class RegexLikeExample {
    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 COUNT(*) FROM CUSTOMERS WHERE REGEXP_LIKE(ADDRESS, 'Mumbai|Delhi')";

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

            if (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("Found " + count + " customers in Mumbai or Delhi.");
            }
        } catch (SQLException e) {
            System.err.println("Database operation failed: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

// Expected Output:
// Found 2 customers in Mumbai or Delhi.

PHP Example (with PDO)

PDO (PHP Data Objects) is a modern, object-oriented interface for accessing databases in PHP. It supports prepared statements to prevent SQL injection.

<?php
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
     echo "Database connection established.\n";

     $pattern = '^.{5}$'; // Names that are exactly 5 characters long
     $stmt = $pdo->prepare('SELECT NAME FROM CUSTOMERS WHERE REGEXP_LIKE(NAME, :pattern)');
     $stmt->execute(['pattern' => $pattern]);

     echo "Customers with 5-letter names:\n";
     while ($row = $stmt->fetch()) {
         echo "- " . $row['NAME'] . "\n";
     }

} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// Expected Output:
// Database connection established.
// Customers with 5-letter names:
// - Komal
// - Muffy