RLIKE Condition

This MariaDB tutorial explains how to use the MariaDB RLIKE condition (to perform regular expression matching) with syntax and examples.

* Not to be confused with the LIKE condition which performs simple pattern matching.

Description

The MariaDB RLIKE condition allows you to perform regular expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the RLIKE condition in MariaDB is:

expression RLIKE pattern

Parameters or Arguments

expression

A character expression such as a column or field.

pattern

The regular expression matching information. It can be a combination of the following:

Value	Description
^	Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.
$	Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression.
*	Matches zero or more occurrences.
+	Matches one or more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
|	Used like an "OR" to specify more than one alternative.
[ ]	Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]	Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..]	Matches one collation element that can be more than one character.
[::]	Matches character classes.
[==]	Matches equivalence classes.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.

Note

• The RLIKE condition performs a case-insensitive match, except when used with binary strings.
• This page applies to MariaDB 10.0.5 and higher, which uses the PCRE regex library. Prior to MariaDB 10.0.5, the RLIKE condition used POSIX 1003.2 compliant regular expression library.
• See also the MariaDB LIKE condition.

Applies To

The RLIKE condition can be used in the following versions of MariaDB:

• MariaDB 10.0.5+

Example - Match on more than one alternative

Let's start by looking how to use the | pattern in the RLIKE condition in MariaDB.

For example:

SELECT product_name

FROM products

WHERE product_name RLIKE 'P(a|e|i)n';

This RLIKE example will return all products that have a product_name that is either Pan, Pen, or Pin. The | pattern tells us to look for the letter "a", "e", or "i".

Example - Match on beginning

Next, let's use the RLIKE condition to match on the beginning of a string.

For example:

SELECT product_id, product_name

FROM products

WHERE product_name RLIKE '^P';

This RLIKE example uses the ^ pattern to match on the start of a string. It will return all product_id and product_name values where the product_name starts with 'P'.

Since the RLIKE condition performs a case-insensitive match (unless binary string), it would be equivalent to the following:

SELECT product_id, product_name

FROM products

WHERE product_name RLIKE '^p';

Both queries would return the same results.

Example - Match on end

Next, let's use the RLIKE condition to match on the end of a string.

For example:

SELECT *

FROM products

WHERE product_name RLIKE 'n$';

This RLIKE example uses the $ pattern to match on the end of a string. It will return all products whose product_name ends with 'n'.

Example - Nonmatching List

Next, let's use the RLIKE condition to define a nonmatching list. This means that you are trying to match any character except for the ones in the list.

For example:

SELECT *

FROM products

WHERE product_name RLIKE 'P[^a]n';

This example uses the [^] pattern to match any character except the ones listed. It will return all records from the products table where the first character of the product_name is 'P', the second character is not 'a', and the third character is 'n'. So this would include values such as 'Pen', 'Pin', 'Pon' or 'Pun', but exclude the value 'Pan' from the result set.

You could further complicate this example, by adding more than one character to not match on.

For example:

SELECT *

FROM products

WHERE product_name RLIKE 'P[^ai]n';

This example will return all records from the products table where the first character of the product_name is 'P', the second character is not 'a' or 'i', and the third character is 'n'. So this would include values such as 'Pen', 'Pon' or 'Pun', but exclude the values 'Pan' and 'Pin' from the result set.

Example - Match Character Classes

Next, let's look at how to use the RLIKE condition to match on character classes in MariaDB.

You can use the following character classes with the RLIKE condition in MariaDB:

Character Class	Description
alnum	Alphanumeric characters
alpha	Alphabetic characters
blank	Whitespace characters
cntrl	Control characters
digit	Digits
graph	Graphic characters
lower	Lowercase alphabetic characters
print	Graphic or space characters
punct	Punctuation characters
space	Characters such as carriage return, newline, space, and tab
upper	Uppercase alphabetic characters
xdigit	Hexadecimal digits

Let's look at an example of how to match on character classes with the RLIKE condition.

For example:

SELECT *

FROM contacts

WHERE address RLIKE '123[:alnum:]*';

In this example, the RLIKE condition will return all records from the contacts table where the address starts with 123 and then the remainder of the characters are alphanumeric characters.

Example - Escaping Characters

One final example will cover explains how to escape special characters when using the RLIKE condition in MariaDB. To escape a special character with the RLIKE condition, you need to preceed the special character with two backslashes \\.

For example:

SELECT *

FROM products

WHERE product_name RLIKE '\\(';

In this example, we have escaped the ( character, so the RLIKE condition will return all records from the products table where the product_name contains a ( character.