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)

MySQL REGEXP and RLIKE Special Characters

Most often used regular expression characters and constructs in MySQL:

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

Resources

MySQL Reference

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference