MySQL REGEXP: Match Text with Regular Expressions

MySQL REGEXP tests whether text matches a regular-expression pattern. It is useful when LIKE wildcards such as % and _ are not expressive enough.

SELECT id,
       name
FROM student
WHERE name REGEXP '^A'
ORDER BY id;

The pattern ^A means "starts with A".

REGEXP vs LIKE Top ↑

The old version of this page described LIKE as matching only the entire value. More precisely, LIKE matches the whole string against a wildcard pattern, while REGEXP searches for a regular-expression match unless anchors restrict it.

SELECT 'plus2net.com' LIKE '2n' AS like_result;

Result: 0

SELECT 'plus2net.com' REGEXP '2n' AS regexp_result;

Result: 1, because the sequence 2n occurs inside the string.

REGEXP Pattern Reference Top ↑

Dot: Match One Character Top ↑

A dot matches one character:

SELECT 'cat' REGEXP 'c.t' AS matched;

The result is true because the dot matches a.

To match an entire three-character name, combine dots with start and end anchors:

SELECT id,
       name
FROM student
WHERE name REGEXP '^...$';

Character Classes Top ↑

Square brackets match one character from a set or range.

Names starting with A or B Top ↑

SELECT id,
       name,
       class,
       mark,
       sex
FROM student
WHERE name REGEXP '^[AB]'
ORDER BY id;

Names starting with a letter from A through F Top ↑

SELECT id,
       name
FROM student
WHERE name REGEXP '^[A-F]';

* : Zero or More Top ↑

* means zero or more repetitions of the preceding expression.

SELECT 'ac' REGEXP 'ab*c' AS zero_b,
       'abc' REGEXP 'ab*c' AS one_b,
       'abbbc' REGEXP 'ab*c' AS many_b;

All three expressions match because the pattern accepts zero, one, or many b characters.

+ : One or More Top ↑

+ requires at least one occurrence of the preceding expression.

SELECT id,
       name,
       class,
       mark,
       sex
FROM student
WHERE name REGEXP 'c+'
ORDER BY id;

Case sensitivity depends on the comparison rules discussed below.

? : Zero or One Top ↑

? makes the preceding expression optional.

SELECT 'color' REGEXP 'colou?r' AS color_match,
       'colour' REGEXP 'colou?r' AS colour_match;

Both values match because u? allows zero or one u.

^ : Start of String Top ↑

Return names that start with A:

SELECT id,
       name,
       class,
       mark,
       sex
FROM student
WHERE name REGEXP '^A'
ORDER BY id;

$ : End of String Top ↑

Return names ending with a:

SELECT id,
       name,
       class,
       mark,
       sex
FROM student
WHERE name REGEXP 'a$'
ORDER BY id;

{n}: Exact Repetition Top ↑

{n} requires exactly n repetitions of the preceding expression.

SELECT 'aaaa' REGEXP '^a{4}$' AS matched;

This matches exactly four a characters.

Match an Exact Number of Characters Top ↑

Four-character names:

SELECT id,
       name,
       class,
       mark,
       sex
FROM student
WHERE name REGEXP '^.{4}$';

The start and end anchors ensure that all four characters make up the complete string.

NOT REGEXP Top ↑

NOT REGEXP returns rows whose value does not match the pattern.

SELECT id,
       name,
       class,
       mark,
       sex
FROM student
WHERE name NOT REGEXP '^[ABGTRJKM]'
ORDER BY id;

The original sample returned names beginning outside that listed group of letters.

Alternation with | Top ↑

The pipe character means "either pattern".

SELECT id,
       name
FROM student
WHERE name REGEXP 'John|Alex'
ORDER BY id;

This matches names containing either John or Alex.

Grouping with Parentheses Top ↑

Parentheses group part of a pattern, which is useful with repetition or alternation.

SELECT 'abab' REGEXP '^(ab){2}$' AS matched;

The grouped expression (ab) is repeated exactly twice.

Case Sensitivity Top ↑

REGEXP case behavior depends on the character set and collation of the expression. With a case-insensitive collation, a lowercase pattern can match uppercase text.

The old page used:

SELECT id,
       name
FROM student
WHERE name REGEXP BINARY '^[Ab]';

Using BINARY can force a binary/case-sensitive comparison, but it is not the only modern option.

REGEXP_LIKE() in MySQL 8 Top ↑

MySQL 8 provides REGEXP_LIKE() as a function form of regular-expression testing.

Case-sensitive match Top ↑

SELECT id,
       name
FROM student
WHERE REGEXP_LIKE(
    name,
    '^[Ab]',
    'c'
)
ORDER BY id;

Case-insensitive match Top ↑

SELECT id,
       name
FROM student
WHERE REGEXP_LIKE(
    name,
    '^[ab]',
    'i'
)
ORDER BY id;

Use the operator form when it is sufficient; use the function form when explicit match options make the query clearer.

NULL Values Top ↑

If the text expression is NULL, REGEXP does not return true or false; the comparison result is NULL.

SELECT NULL REGEXP 'A' AS result;

Therefore:

WHERE name NOT REGEXP '^A'

does not automatically include rows where name is NULL.

If NULL must be included:

WHERE name NOT REGEXP '^A'
   OR name IS NULL

See SQL NULL values.

PHP PDO Example Top ↑

When the pattern comes from the application, bind it as a value:

<?php
$pattern='^[AB]';

$sql="SELECT id, name, class, mark
      FROM student
      WHERE name REGEXP :pattern
      ORDER BY id";

$stmt=$dbo->prepare($sql);
$stmt->bindValue(
    ':pattern',
    $pattern,
    PDO::PARAM_STR
);
$stmt->execute();

foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['name'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .'</p>';
}

A prepared statement protects the SQL value boundary, but it does not make an arbitrary user-supplied regular expression inexpensive or suitable. If users can submit patterns, validate length and application-specific complexity limits.

Performance Notes Top ↑

• Regular-expression matching is generally more expensive than exact equality or simple indexable prefix/range conditions.
• Do not replace a simple equality or LIKE requirement with REGEXP merely because REGEXP is more powerful.
• A leading anchored literal pattern such as ^ABC communicates more restriction than an unanchored pattern, but do not assume every REGEXP pattern will use a normal B-tree index efficiently.
• For large tables, use EXPLAIN and measure the actual query.
• Filter other selective columns first where the query logic allows it.
• Bound the size and complexity of user-supplied patterns in public search forms.

Prefer LIKE when it is enough Top ↑

If the requirement is simply "starts with John", this is often clearer:

SELECT id,
       name
FROM student
WHERE name LIKE 'John%';

Use REGEXP when the search genuinely requires richer pattern logic.

Common REGEXP Mistakes Top ↑

Confusing * and ? Top ↑

* means zero or more. ? means zero or one.

Using a pattern that can match an empty string Top ↑

Patterns such as b* and c? can succeed without consuming a character, so they may match nearly every non-NULL value.

Forgetting ^ and $ Top ↑

Without anchors, REGEXP normally searches for the pattern anywhere in the value.

Assuming all REGEXP searches are case-sensitive Top ↑

Case behavior depends on collation and, with REGEXP_LIKE(), explicit match options.

Using BINARY as the only case-sensitive approach Top ↑

BINARY is one option, but MySQL 8 also provides explicit case control through REGEXP_LIKE().

Expecting NOT REGEXP to include NULL Top ↑

NULL comparisons remain NULL. Add an explicit OR column IS NULL when required.

Using REGEXP for simple exact matching Top ↑

Use = for exact equality and LIKE for simple wildcard patterns. Choose REGEXP only when regular-expression features are needed.

Accepting unlimited user regex patterns Top ↑

Prepared statements prevent SQL injection through the value boundary, but application-level limits are still useful to prevent overly expensive searches.

Related Tutorials and SQL Dump Top ↑

Download SQL dump of the student table

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: