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