How NOT REGEXP Works in MariaDB

by

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

If the string matches the regular expression provided, the result is 0, otherwise it’s 1. This is the opposite result to what the REGEXP would return (when it isn’t prefixed with NOT).

Syntax

The syntax goes like this:

expr NOT REGEXP 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 REGEXP pat)

Example

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

SELECT 'Bread' NOT REGEXP '^Br';

Result:

+--------------------------+
| 'Bread' NOT REGEXP '^Br' |
+--------------------------+
|                        0 |
+--------------------------+

Here, the pattern is matched if the input string starts with Br. It does, but because we use NOT REGEXP, we get a negative result (0).

The above statement is the equivalent of doing this:

SELECT NOT ('Bread' REGEXP '^Br');

Result:

+----------------------------+
| NOT ('Bread' REGEXP '^Br') |
+----------------------------+
|                          0 |
+----------------------------+

Compared to REGEXP

In this example we compare the results from REGEXP and NOT REGEXP:

SELECT 
  'Bread' REGEXP '^Br' AS "REGEXP",
  'Bread' NOT REGEXP '^Br' AS "NOT REGEXP";

Result:

+--------+------------+
| REGEXP | NOT REGEXP |
+--------+------------+
|      1 |          0 |
+--------+------------+

A Positive Result

The previous examples resulted in 0 for NOT REGEXP, 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 
  'Sofa' REGEXP '^Br' AS "REGEXP",
  'Sofa' NOT REGEXP '^Br' AS "NOT REGEXP";

Result:

+--------+------------+
| REGEXP | NOT REGEXP |
+--------+------------+
|      0 |          1 |
+--------+------------+