How NOT RLIKE Works in MySQL

In MySQL, 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 1 – Basic Usage

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

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

Result:

+--------+
| Result |
+--------+
|      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$') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 2 – 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 |
+-------+-----------+

Example 3 – 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

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

Firstly, RLIKE is a synonym of the REGEXP_LIKE() function (as is REGEXP).

Secondly, NOT RLIKE is the equivalent of NOT REGEXP.

Thirdly, RLIKE, REGEXP, and REGEXP_LIKE() 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)
NOT REGEXP_LIKE(expr, 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',
  NOT REGEXP_LIKE('Car', '^C') AS 'Result 5';

Result:

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