MySQL - REGEXP, RLIKE - Guide, Examples and Alternatives

REGEXP and RLIKE operators check whether the string matches pattern containing a regular expression.

Quick Example:

  -- Find cities that start with A
  SELECT name FROM cities WHERE name REGEXP '^A';

Overview:

Synonyms REGEXP and RLIKE are synonyms
Syntax string [NOT] REGEXP pattern
Return 1 string matches pattern
0 string does not match pattern
NULL string or pattern are NULL
Case Sensitivity REGEXP and RLIKE are not case sensitive, except when used for
with BINARY and VARBINARY data types
Negation string [NOT] REGEXP pattern is equivalent to NOT (string REGEXP pattern)
Alternatives LIKE operator with wildcard character % (zero or more any characters)
and _ (any one character)

Version: MySQL 5.6

MySQL REGEXP and RLIKE Special Characters

Most often used regular expression characters and constructs in MySQL (full list...):

| OR operator when multiple patterns are specified
abc Check if string contains a substring abc
^abc Check if string starts with abc

MySQL REGEXP and RLIKE Details

Assume that we have the following table definition and data in MySQL:

  CREATE TABLE cities (name VARCHAR(80));
 
  INSERT INTO cities VALUES ('London');
  INSERT INTO cities VALUES ('Paris');
  INSERT INTO cities VALUES ('Rome');

Find Strings Containing Specified Characters

Find cities containing characters A, B or R at any position:

  SELECT name FROM cities WHERE name RLIKE 'A|B|R';
  -- or
  SELECT name FROM cities WHERE name REGEXP 'A|B|R';
  -- or using LIKE
  SELECT name FROM cities WHERE name LIKE '%A%' OR name LIKE '%B%' OR name LIKE '%R%'

All queries return:

name
Paris
Rome

Note:

  • RLIKE, REGEXP and LIKE are case insensitive
  • RLIKE and REGEXP searches a substring, not the whole word if control characters are missed
  • LIKE requires surrounding % to search a substring

Find Strings Starting With Specified Characters

Find cities starting with characters A, B or R:

  SELECT name FROM cities WHERE name RLIKE '^A|^B|^R';
  -- or
  SELECT name FROM cities WHERE name REGEXP '^A|^B|^R';
  -- or using LIKE
  SELECT name FROM cities WHERE name LIKE 'A%' OR name LIKE 'B%' OR name LIKE 'R%'

All queries return:

name
Rome

MySQL REGEXP and RLIKE in Other Databases

Regular expressions in other databases:

Oracle

REGEXP_LIKE(string, pattern, parameters) Case-sensitive and case-insensitive options

MySQL REGEXP and RLIKE Conversion to Other Databases

Conversion of MySQL REGEXP and RLIKE:

Oracle:

Oracle provides REGEXP_LIKE function that supports similar syntax and behavior for regular expressions, but it is case sensitive by default, so 'i' parameter is required for case-insensitive matching:

   -- Find cities containing characters A, B or R at any position
   SELECT name FROM cities WHERE REGEXP_LIKE(name, 'A|B|R', 'i'); 
 
   -- Find cities that starts with characters A, B or R
   SELECT name FROM cities WHERE REGEXP_LIKE(name, '^A|^B|^R', 'i');

Convert Online

Resources