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