How NOT RLIKE Works in MariaDB

by

In MariaDB, NOT RLIKE is a negation of the RLIKE operator.

In other words, any time the RLIKE operator would return 1, NOT RLIKE will return 0.

Syntax

The syntax goes like this:

expr NOT RLIKE pat

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

It’s the equivalent of doing the following:

NOT (expr RLIKE pat)

Example

Here’s an example of using this in a SELECT statement:

SELECT 'Coffee' NOT RLIKE '^C.*e$';

Result:

+-----------------------------+
| 'Coffee' NOT RLIKE '^C.*e$' |
+-----------------------------+
|                           0 |
+-----------------------------+

Here, the pattern is matched if the input string starts with C and ends with e. It does, but because we use NOT RLIKE, we get a negative result (0).

The above statement is the equivalent of doing this:

SELECT NOT ('Coffee' RLIKE '^C.*e$');

Result:

+-------------------------------+
| NOT ('Coffee' RLIKE '^C.*e$') |
+-------------------------------+
|                             0 |
+-------------------------------+

Compared to RLIKE

Here we compare the results from RLIKE with NOT RLIKE:

SELECT 
  'Coffee' RLIKE '^C.*e$' AS "RLIKE",
  'Coffee' NOT RLIKE '^C.*e$' AS "NOT RLIKE";

Result:

+-------+-----------+
| RLIKE | NOT RLIKE |
+-------+-----------+
|     1 |         0 |
+-------+-----------+

A Positive Result

The previous examples resulted in 0 for NOT RLIKE, because the string did actually match the pattern. Here’s an example where we get a 1, which indicates that the string doesn’t match:

SELECT 
  'Funny' RLIKE '^C.*e$' AS "RLIKE",
  'Funny' NOT RLIKE '^C.*e$' AS "NOT RLIKE";

Result:

+-------+-----------+
| RLIKE | NOT RLIKE |
+-------+-----------+
|     0 |         1 |
+-------+-----------+

Alternatives

MariaDB includes many functions and operators that essentially do the same thing, and this also applies to NOT RLIKE.

  1. RLIKE is a synonym of the REGEXP operator.
  2. NOT RLIKE is the equivalent of NOT REGEXP.
  3. RLIKE and REGEXP can be negated by simply using the NOT logical operator.

Therefore, all of the following are equivalent:

expr NOT RLIKE pat
expr NOT REGEXP pat
NOT (expr RLIKE pat)
NOT (expr REGEXP pat)

And here’s an example to demonstrate:

SELECT 
  'Car' NOT RLIKE '^C' AS "Result 1",
  'Car' NOT REGEXP '^C' AS "Result 2",
  NOT ('Car' RLIKE '^C') AS "Result 3",
  NOT ('Car' REGEXP '^C') AS "Result 4";

Result:

+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 |
+----------+----------+----------+----------+
|        0 |        0 |        0 |        0 |
+----------+----------+----------+----------+