In MariaDB, the RLIKE operator is used to determine whether or not a string matches a regular expression. It’s a synonym for REGEXP.

If the string matches the regular expression provided, the result is 1, otherwise it’s 0.

Syntax

The syntax goes like this:

expr RLIKE pat

Where expr is the input string and pat is the regular expression for which you’re testing the string against.

Example

Here’s an example of how to use this operator in a SELECT statement:

SELECT 'Tweet' RLIKE '^Tw.*t$';

Result:

+-------------------------+
| 'Tweet' RLIKE '^Tw.*t$' |
+-------------------------+
|                       1 |
+-------------------------+

In this case, the return value is 1 which means that the input string matched the regular expression. In particular, we specified that the input string should start with Tw and end with t (this is because we started the pattern with ^Tw and ended it with t$). The . part specifies any character, and * specifies that it could be zero to any number of that (any) character. So .* means that there can be no characters, one character, or many characters in between the start and end.

Here’s what happens if we drop the *:

SELECT 'Tweet' RLIKE '^Tw.t$';

Result:

+------------------------+
| 'Tweet' RLIKE '^Tw.t$' |
+------------------------+
|                      0 |
+------------------------+

The return result is 0 which means no match. This is because . specifies only one instance of any character. Our input string contains two instances.

Here are some permutations:

SELECT 
  'Twet' RLIKE '^Tw.t$' AS "Twet",
  'Twit' RLIKE '^Tw.t$' AS "Twit",
  'Twt' RLIKE '^Tw.t$' AS "Twt",
  'Tw.t' RLIKE '^Tw.t$' AS "Tw.t";

Result:

+------+------+-----+------+
| Twet | Twit | Twt | Tw.t |
+------+------+-----+------+
|    1 |    1 |   0 |    1 |
+------+------+-----+------+