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 | +--------+------------+