MySQL - RLIKE Operator

MySQL: Pattern Matching with Regular Expressions

MySQL provides powerful pattern matching capabilities through regular expressions. This allows you to perform complex searches that go far beyond simple LIKE clauses. The primary operators for this are RLIKE and its alias, REGEXP.

A regular expression (or regex) is a sequence of characters that specifies a search pattern. It’s a standard tool for string manipulation and validation used across many programming languages and tools.

The RLIKE and REGEXP Operators

The RLIKE operator (and its identical synonym REGEXP) is used in a WHERE clause to match string values against a regular expression pattern. It returns 1 (true) if the pattern matches any part of the string, and 0 (false) otherwise.

Syntax

SELECT column_list FROM table_name
WHERE string_column RLIKE 'pattern';

Common Regex Patterns

Here are some of the most frequently used patterns:

Pattern	What it matches
^	Beginning of the string. ^a matches ‘apple’ but not ‘banana’.
$	End of the string. a$ matches ‘banana’ but not ‘apple’.
.	Any single character. h.t matches ‘hat’, ‘hot’, and ‘h/t’.
*	Zero or more occurrences of the preceding element. a* matches ”, ‘a’, ‘aa’.
+	One or more occurrences of the preceding element. a+ matches ‘a’, ‘aa’ but not ”.
[abc]	Any single character in the set. [hc]at matches ‘hat’ and ‘cat’.
[^abc]	Any single character NOT in the set. [^b]at matches ‘hat’, ‘cat’ but not ‘bat’.
[a-z]	Any character in the specified range. [0-9] matches any digit.
p1|p2	Alternation. Matches either p1 or p2. cat|dog matches ‘cat’ or ‘dog’.

Practical Examples

Let’s use a CUSTOMERS table for our examples.

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    EMAIL VARCHAR(255)
);

INSERT INTO CUSTOMERS(NAME, EMAIL) VALUES
('Ramesh', 'ramesh@example.com'),
('Khilan', 'khilan.k@test.org'),
('Kaushik', 'kaushik@example.com'),
('Chaitali', 'chaitali_t@web.net'),
('Komal', 'komal@test.org');

Example 1: Finding Names Starting with ‘K’

SELECT NAME, EMAIL FROM CUSTOMERS WHERE NAME RLIKE '^K';

Example 2: Finding Names Ending with ‘l’

SELECT NAME, EMAIL FROM CUSTOMERS WHERE NAME RLIKE 'l$';

Example 3: Finding ‘.org’ Email Addresses

This finds any customer whose email contains ‘.org’.

SELECT NAME, EMAIL FROM CUSTOMERS WHERE EMAIL RLIKE '\.org$';
-- Note: We escape the dot (\.) because '.' is a special regex character.

Example 4: Using Alternation

Find names that contain either ‘sh’ or ‘al’.

SELECT NAME, EMAIL FROM CUSTOMERS WHERE NAME RLIKE 'sh|al';

Modern Regex Functions (MySQL 8.0+)

MySQL 8.0 introduced a set of more powerful and specific regular expression functions. These are generally preferred over RLIKE/REGEXP in modern development as they offer more control and functionality.

• REGEXP_LIKE(expr, pat): An explicit function-based equivalent of the REGEXP operator. Good for clarity.
• REGEXP_INSTR(expr, pat): Returns the starting index of the substring matching the pattern.
• REGEXP_REPLACE(expr, pat, repl): Replaces occurrences of a pattern with a new string.
• REGEXP_SUBSTR(expr, pat): Extracts the substring that matches the pattern.

Example: Extracting Domain Names

Let’s use REGEXP_SUBSTR to extract just the domain name from the email addresses. The pattern @(.*) captures everything after the ’@’ symbol.

SELECT NAME, EMAIL, REGEXP_SUBSTR(EMAIL, '@(.*)') AS domain
FROM CUSTOMERS;

Example: Anonymizing Emails

Let’s use REGEXP_REPLACE to hide the username part of the email. The pattern ^.*@ matches everything from the start of the string up to and including the ’@’.

SELECT NAME, EMAIL, REGEXP_REPLACE(EMAIL, '^.*@', '******@') AS anonymized_email
FROM CUSTOMERS;

Performance and Best Practices

• Performance Warning: Regular expression searches cannot use standard B-Tree indexes effectively. A query with RLIKE on a large table will almost always result in a full table scan, which can be very slow.
• Use LIKE for Simple Patterns: If your pattern is simple (e.g., starts with, ends with, contains), use LIKE. col LIKE 'K%' is much faster than col RLIKE '^K'.
• Consider Full-Text Search: For complex word and phrase searching in large text fields, MySQL’s Full-Text Search (FTS) is a much more performant and feature-rich alternative to regex.
• Use Anchors: When possible, use ^ (start) and $ (end) anchors. This can help the regex engine fail faster on non-matching strings.

Using RLIKE in Application Code

When using regex from your application, it’s critical to use parameterized queries to prevent SQL injection. Even though the pattern is a string, it should be passed as a parameter.

Node.js (with mysql2)
Python (with mysql-connector-python)



### Code
This example finds customers with a `.com` email address.

```javascript
// Filename: queryRlike.js
const mysql = require('mysql2/promise');

async function findCustomersByEmailPattern(pattern) {
  let connection;
  try {
    const pool = mysql.createPool({
      host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS'
    });

    const sql = "SELECT NAME, EMAIL FROM CUSTOMERS WHERE EMAIL RLIKE ?";
    const params = [pattern];

    const [rows, fields] = await pool.query(sql, params);

    console.log(`Customers with email matching pattern '${pattern}':`);
    rows.forEach(row => {
      console.log(`Name: ${row.NAME}, Email: ${row.EMAIL}`);
    });

    await pool.end();

  } catch (error) {
    console.error('Database query failed:', error);
  }
}

// Find all .com email addresses. Note the double backslash in the string for escaping.
findCustomersByEmailPattern('\\.com$');

Code

This example finds customers with a .com email address.

query_rlike.py

import mysql.connector
from mysql.connector import Error

def find_customers_by_email_pattern(pattern):
    """ Finds customers by a regex pattern on their email. """
    try:
        with mysql.connector.connect(
            host='localhost', user='root', password='password', database='TUTORIALS'
        ) as connection:

            query = "SELECT NAME, EMAIL FROM CUSTOMERS WHERE EMAIL RLIKE %s"
            params = (pattern,)

            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, params)
                results = cursor.fetchall()

                print(f"Customers with email matching pattern '{pattern}':")
                for row in results:
                    print(f"Name: {row['NAME']}, Email: {row['EMAIL']}")

    except Error as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    # Find all .com email addresses
    find_customers_by_email_pattern(r'\.com$')