MariaDB REGEXP Operator

In MariaDB, the REGEXP operator checks whether a string matches a regular expression.

Unlike the LIKE operator, the REGEXP operator can check whether parts of a string match a pattern.

The negation of the REGEXP operator is NOT REGEXP.

The REGEXP operator is exactly the same as RLIKE.

MariaDB REGEXP Syntax

Here is the syntax for the MariaDB REGEXP operator:

str REGEXP regexp

Parameters

str

Optional. a string.

regexp

Optional. regular expression.

Return value

The MariaDB REGEXP operator returns 1 if the str string matches the regular expression regexp, otherwise returns 0.

REGEXP returns NULL if any argument is NULL.

Regular Expression Basics

MariaDB all you use many wildcards to build regular expressions, the following table is a brief introduction:

character describe
^ Matches the beginning of the string.
$ Matches the end of the string.
* Matches the preceding subexpression zero or more times.
+ Matches the preceding subexpression one or more times.
? Matches the preceding subexpression zero or one time.
{n} n is a non-negative integer. Matches the preceding subexpression n times.
{n,} n is a non-negative integer. Matches the preceding subexpression at least n times.
{n,m} Both m and n are non-negative integers, and n<=m. Matches at least n times and at most m times.
[xyz] A collection of matching characters.
[^xyz] A collection of excluded characters.
[a-z] Matches a range of characters.
[^a-z] Range of excluded characters.
\b Matches a word boundary, that is, the position between a word and a space.
\B Matches non-word boundaries.
\d Matches a numeric character. Equivalent to [0-9].
\D Matches a non-numeric character. Equivalent to [^0-9].
\f Matches a form feed character. Equivalent to \x0c and \cL.
\n Matches a newline character. Equivalent to \x0a and \cJ.
\r Matches a carriage return. Equivalent to \x0d and \cM.
\s Matches any whitespace character, including spaces, tabs, form feeds, and so on. Equivalent to [ \f\n\r\t\v].
\S Matches any non-whitespace character. Equivalent to [^ \f\n\r\t\v].
\t Matches a tab character. Equivalent to \x09 and \cI.
\v Matches a vertical tab character. Equivalent to \x0b and \cK.
\w Matches any word character including an underscore. Equivalent to [A-Za-z0-9_].
\W Matches any non-word character. Equivalent to [^A-Za-z0-9_].

A specific character matches the character itself.

MariaDB REGEXP Examples

Below are some examples of MariaDB REGEXP operators.

Basic usage

SELECT
  'ab' REGEXP 'a',
  'ab' REGEXP 'b',
  'ab' REGEXP 'c';

Output:

+-----------------+-----------------+-----------------+
| 'ab' REGEXP 'a' | 'ab' REGEXP 'b' | 'ab' REGEXP 'c' |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+

In this example, a, b and c are all regular expressions. 'ab' can be matched in a the b, but not c.

Matching any character

SELECT
  'ab' REGEXP '.',
  'ab' REGEXP '.*';

Output:

+-----------------+------------------+
| 'ab' REGEXP '.' | 'ab' REGEXP '.*' |
+-----------------+------------------+
|               1 |                1 |
+-----------------+------------------+

In this example:

  • . matches any character.
  • .* matches any number of any characters.

Matching the beginning of a string

The ^ wildcard matches the beginning of a string, as follows:

SELECT
  'Hello' REGEXP '^H',
  'Hello' REGEXP '^A';

Output:

+---------------------+---------------------+
| 'Hello' REGEXP '^H' | 'Hello' REGEXP '^A' |
+---------------------+---------------------+
|                   1 |                   0 |
+---------------------+---------------------+

In this example, ^H matches a string beginning with H, so 'Hello' REGEXP '^H' returned 1, while is 'Hello' REGEXP '^A' returned 0.

Matching the end of a string

The $ wildcard match the end of a string, as follows:

SELECT
  'Hello' REGEXP 'lo$',
  'Hello' REGEXP 'lH$';

Output:

+----------------------+----------------------+
| 'Hello' REGEXP 'lo$' | 'Hello' REGEXP 'lH$' |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+

In this example, lo$ matches a string ends with lo, so 'Hello' REGEXP 'lo$' returned 1, while 'Hello' REGEXP 'lH$' returned 0.

Matching numbers

The \d wildcards matches numbers, and \D matches numbers, as follows:

SELECT
  '123' REGEXP '^\\d+$',
  'abc' REGEXP '^\\D+$';

Output:

+-----------------------+-----------------------+
| '123' REGEXP '^\\d+$' | 'abc' REGEXP '^\\D+$' |
+-----------------------+-----------------------+
|                     1 |                     1 |
+-----------------------+-----------------------+

You can use [0-9] instead of \\d, and use [^0-9] instead of \\D.

NULL value

If the expression or pattern is NULL, the result is NULL:

SELECT
  null REGEXP '.*',
  'abc' REGEXP null;

Output:

+------------------+-------------------+
| null REGEXP '.*' | 'abc' REGEXP null |
+------------------+-------------------+
|             NULL |              NULL |
+------------------+-------------------+

Conclusion

In MariaDB, the REGEXP operator checks whether a string matches a regular expression.