MySQL - NOT REGEXP Operator

MySQL - The NOT REGEXP Operator

Introduction to Regular Expressions in MySQL

A regular expression (regex) is a sequence of characters that specifies a search pattern. It’s a powerful tool for advanced text matching, far more flexible than the LIKE operator’s simple wildcards (% and _).

The NOT REGEXP operator in MySQL (and its alias NOT RLIKE) is used to filter results, returning only the rows that do not match a specified regular expression pattern.

Syntax of NOT REGEXP

SELECT column_list
FROM table_name
WHERE string_column NOT REGEXP 'pattern';

Some basic regex metacharacters you’ll often use:

• ^: Anchors the pattern to the beginning of the string.
• $: Anchors the pattern to the end of the string.
• .: Matches any single character.
• [...]: Matches any character within the brackets (e.g., [aeiou]).
• |: Acts as an OR condition (e.g., a|b matches ‘a’ or ‘b’).

NOT REGEXP vs. NOT LIKE

Choose the right tool for the job:

• Use NOT LIKE for simple wildcard searches. It’s faster for basic patterns. Example: WHERE name NOT LIKE 'A%' (name doesn’t start with ‘A’).
• Use NOT REGEXP for complex pattern matching that LIKE cannot handle. Example: WHERE name NOT REGEXP '^A|^B' (name doesn’t start with ‘A’ or ‘B’).

Practical Examples

First, let’s create and populate a users table for our examples.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

INSERT INTO users (username, email)
VALUES
    ('alex', 'alex@example.com'),
    ('ben_carter', 'ben.carter@work.net'),
    ('charlie', 'charlie99@example.com'),
    ('david_lee', 'david.lee@web.org'),
    ('eva', 'eva@example.com');

Our users table now contains:

id	username	email
1	alex	alex@example.com
2	ben_carter	ben.carter@work.net
3	charlie	charlie99@example.com
4	david_lee	david.lee@web.org
5	eva	eva@example.com

Example 1: Find usernames that do not contain numbers

SELECT username FROM users
WHERE username NOT REGEXP '[0-9]';

username
alex
ben_carter
david_lee
eva

Example 2: Find emails that are not from ‘example.com’

SELECT email FROM users
WHERE email NOT REGEXP 'example\.com$';

Note: The dot . is a special regex character, so we escape it with a backslash \ to match a literal dot.

email
ben.carter@work.net
david.lee@web.org

Example 3: Find usernames that do not contain an underscore or a dot

SELECT username FROM users
WHERE username NOT REGEXP '_|\.';

username
alex
charlie
eva

Modern Regex Functions in MySQL 8+

Starting with MySQL 8.0, a new set of more powerful, ICU-based regular expression functions were introduced. These offer better Unicode support and performance. While NOT REGEXP is still perfectly valid, for new projects on MySQL 8+, you should consider using these functions.

-- Old way
SELECT username FROM users WHERE username NOT REGEXP '[0-9]';

-- Modern way using REGEXP_LIKE()
SELECT username FROM users WHERE NOT REGEXP_LIKE(username, '[0-9]');

Other modern functions include REGEXP_INSTR(), REGEXP_REPLACE(), and REGEXP_SUBSTR().

Using NOT REGEXP in Client Programs

Here’s how to execute a NOT REGEXP query from various programming languages, using modern, secure practices.

Python
Node.js
PHP
Java




# Using mysql-connector-python with prepared statements

import mysql.connector

def find_users_without_numbers():
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS' # Assuming the 'users' table is here
    }
    query = "SELECT id, username FROM users WHERE username NOT REGEXP %s"
    pattern = '[0-9]'

    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute(query, (pattern,))
                results = cursor.fetchall()
                print("Users with no numbers in username:")
                for row in results:
                    print(row)
    except mysql.connector.Error as err:
        print(f"Database Error: {err}")

find_users_without_numbers()




// Using mysql2/promise with async/await

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

async function findUsersWithoutNumbers() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS' // Assuming the 'users' table is here
        });

        const query = "SELECT id, username FROM users WHERE username NOT REGEXP ?";
        const pattern = '[0-9]';

        const [rows] = await connection.execute(query, [pattern]);
        console.log("Users with no numbers in username:");
        console.log(rows);

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

findUsersWithoutNumbers();




// Using PDO for secure queries

<?php
$dsn = "mysql:host=localhost;dbname=TUTORIALS;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

try {
    $pdo = new PDO($dsn, 'root', 'password', $options);

    $query = "SELECT id, username FROM users WHERE username NOT REGEXP ?";
    $pattern = '[0-9]';

    $stmt = $pdo->prepare($query);
    $stmt->execute([$pattern]);

    $results = $stmt->fetchAll();

    echo "Users with no numbers in username:\n";
    print_r($results);

} catch (PDOException $e) {
    die("DB Connection Error: " . $e->getMessage());
}




// Using JDBC with try-with-resources and PreparedStatement

import java.sql.*;

public class NotRegexpExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void findUsersWithoutNumbers() {
        String sql = "SELECT id, username FROM users WHERE username NOT REGEXP ?";
        String pattern = "[0-9]";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, pattern);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Users with no numbers in username:");
                while (rs.next()) {
                    System.out.printf("ID: %d, Username: %s%n",
                            rs.getInt("id"),
                            rs.getString("username"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        findUsersWithoutNumbers();
    }
}