MariaDB: REGEXP Function

This MariaDB tutorial explains how to use the MariaDB REGEXP function with syntax and examples.

Description

The MariaDB REGEXP function allows you to perform regular expression matching.

Syntax

The syntax for the REGEXP function in MariaDB is:

expression REGEXP 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 is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \ .
[..]	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 REGEXP function 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 REGEXP function used POSIX 1003.2 compliant regular expression library.

Applies To

The REGEXP function 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 with the REGEXP function in MariaDB.

For example:

SELECT product_name
FROM products
WHERE product_name REGEXP 'P(a|e|i)n';

This REGEXP 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 REGEXP function to match on the beginning of a string.

For example:

SELECT product_id, product_name
FROM products
WHERE product_name REGEXP '^P';

This REGEXP 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 REGEXP function 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 REGEXP '^p';

Both queries would return the same results.

Example - Match on end

Next, let's use the REGEXP function to match on the end of a string.

For example:

SELECT *
FROM products
WHERE product_name REGEXP 'n$';

This REGEXP 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 REGEXP function 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 REGEXP '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 REGEXP '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 REGEXP function to match on character classes in MariaDB.

You can use the following character classes with the REGEXP function in MariaDB:

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

For example:

SELECT *
FROM contacts
WHERE address REGEXP '123[:alnum:]*';

In this example, the REGEXP function 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 REGEXP function in MariaDB. To escape a special character with the REGEXP function, you need to preceed the special character with two backslashes \\\\.

For example:

SELECT *
FROM products
WHERE product_name REGEXP '\\\\(';

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