How NOT REGEXP Works in MySQL

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

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

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

SELECT 'Bread' NOT REGEXP '^Br' AS 'Result';

Result:

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

Result:

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

Example 2 – Compared to REGEXP

To make it clear, here 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 |
+--------+------------+

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