SQL RLIKE

What is SQL RLIKE?

RLIKE returns true when a string matches a regular-expression pattern, otherwise false.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RLIKE:

SQL RLIKE Full Explanation

RLIKE is a MySQL and MariaDB comparison operator that performs pattern matching using regular expressions. It is functionally synonymous with the REGEXP operator. When evaluated, RLIKE converts both operands to strings and applies the database’s regex engine (based on Henry Spencer’s POSIX extended implementation). The expression yields 1 (true) if the pattern matches any part of the string and 0 (false) otherwise. Patterns are case-insensitive unless the collation or the REGEXP_LIKE() variant specifies otherwise. RLIKE works only in a WHERE, HAVING, ON, or SELECT context and cannot be used to write to data. Null in either operand returns NULL. Performance can degrade on large tables without indexes on deterministic predicates preceding RLIKE in the WHERE clause.

SQL RLIKE Syntax

string_expression RLIKE pattern_expression

SQL RLIKE Parameters

string_expression (STRING) - The column or literal value to test.
pattern_expression (STRING) - The regular-expression pattern to match against the string.

Example Queries Using SQL RLIKE

-- Boolean evaluation
SELECT 'cat' RLIKE '^c.*$' AS is_match;            

-- Filter rows by regex
SELECT email 
FROM   users 
WHERE  email RLIKE '@example\\.(com|org)$';

-- Case sensitivity controlled by collation
SELECT name 
FROM   products 
WHERE  name COLLATE utf8mb4_bin RLIKE '^A';

Expected Output Using SQL RLIKE

First query returns 1
Second query returns only emails ending in @example
com or @example
org
Third query returns product names starting with uppercase A only

Use Cases with SQL RLIKE

Filtering text columns with complex patterns (emails, phone numbers, IDs)
Quick data validation without writing stored procedures
Auditing values that deviate from naming conventions
Ad-hoc data exploration in analytics queries

Common Mistakes with SQL RLIKE

Forgetting to escape backslashes, especially in client libraries that also escape them
Assuming RLIKE is case-insensitive in binary collations
Expecting full PCRE features; only POSIX extended syntax is supported
Using RLIKE in UPDATE without a WHERE clause filter (it will not modify strings)

First Introduced In

MySQL 3.23.4

Frequently Asked Questions

What is the difference between RLIKE and LIKE?

LIKE matches simple wildcards (%) and (_) while RLIKE evaluates full regular expressions, enabling far more complex pattern checks.

Is RLIKE case sensitive?

It depends on collation. Case-insensitive collations such as utf8mb4_general_ci make RLIKE case-insensitive. Binary collations like utf8mb4_bin enforce case sensitivity.

How can I escape special characters inside a regex pattern?

Escape regex meta-characters with a backslash. Because SQL strings also treat backslash as an escape, provide two backslashes (\\) so one reaches the regex engine.

Does RLIKE slow down queries?

Yes when applied to large unindexed text columns. Combine it with other selective predicates or computed columns to minimize full table scans.