In MariaDB, the NOT LIKE operator is used to test whether or not a string does not match a pattern. It returns the inverse of the LIKE operator. It’s the same as applying the NOT operator against the whole LIKE expression.
A pattern can include regular characters, as well the % and _ wildcard characters.
Those wildcard characters are explained in the following table.
| Wildcard Character | Description |
|---|---|
% | Matches any string of zero or more characters. It can be used as either a prefix or a suffix, and it can also be used in the middle of a string. |
_ | Matches any single character. |
Syntax
The syntax goes like this:
expr NOT LIKE pat [ESCAPE 'escape_char']Example
Suppose we have the following table:
SELECT * FROM Pets;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
Here’s an example of using NOT LIKE against that table:
SELECT *
FROM Pets
WHERE PetName NOT LIKE 'F%';Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
In this example, I used NOT LIKE in conjunction with the % wildcard operator to return pets whose name does not start with the letter F.
This is the opposite result set we would have got if we’d just used LIKE. In that case, we would only get results where the pet’s name starts with the letter F.
Case Sensitivity
NOT LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive.
Therefore, we can change the previous example to use a lowercase f, and still get the same result:
SELECT *
FROM Pets
WHERE PetName NOT LIKE 'f%';Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
However, we can force a case-sensitive match by using the COLLATE clause with a binary collation. Alternatively, you can use CAST() to coerce it to a binary string.
Example:
SELECT *
FROM Pets
WHERE PetName NOT LIKE 'f%' COLLATE utf8_bin;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
This time, the whole table was returned. This is because nothing matched the lower case f.
But if we change it to an uppercase F:
SELECT *
FROM Pets
WHERE PetName NOT LIKE 'F%' COLLATE utf8_bin;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
We get five matches, as expected. The rows where the pet name starts with F are excluded.
Numeric Arguments
Numeric arguments are coerced to binary strings.
SELECT *
FROM Pets
WHERE PetId NOT LIKE 1;Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +-------+-----------+---------+---------+------------+
Dates
Here’s a date example:
SELECT *
FROM Pets
WHERE DOB NOT LIKE '2020%';Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | +-------+-----------+---------+---------+------------+
You can also use the NOT LIKE operator against the result of date functions, such as DAYNAME(), MONTHNAME(), etc.
SELECT *
FROM Pets
WHERE MONTHNAME(DOB) NOT LIKE 'Nov%';Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +-------+-----------+---------+---------+------------+
The _ Wildcard Operator
The underscore (_) wildcard operator matches any single character.
Example:
SELECT *
FROM Pets
WHERE PetName NOT LIKE '_e%';Result:
+-------+-----------+---------+---------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | +-------+-----------+---------+---------+------------+ | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | +-------+-----------+---------+---------+------------+
In this case, we returned pet names where the second character is not an e.
The Escape Character
Sometimes you may find yourself in the situation where you need to search for an underscore or a percentage sign. In such cases, you can use a backslash (\) to escape these characters. This will prevent them from being interpreted as wildcard characters.
Suppose we have the following table:
SELECT * FROM Owners;Result:
+---------+-----------+----------+----------------+--------------------+ | OwnerId | FirstName | LastName | Phone | Email | +---------+-----------+----------+----------------+--------------------+ | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Simpson | (489) 591-0408 | NULL | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +---------+-----------+----------+----------------+--------------------+
Notice that Owner 2 (Bart) has an email address that contains an underscore.
Here’s an example of what happens when we don’t escape an underscore:
SELECT * FROM Owners
WHERE Email NOT LIKE 'b_%';Result:
+---------+-----------+----------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | +---------+-----------+----------+----------------+-------------------+ | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | +---------+-----------+----------+----------------+-------------------+
In this case, it returned just two rows. It excluded the two rows where the email address starts with a b, and is followed by one other character, and then any number of characters. It also excluded the rows with a null email address.
However, because we didn’t escape the underscore, it didn’t matter what the second character was – it matched any character. The fact that the first row contains an underscore for the second character is just a coincidence. It would have matched even if it was something else.
But what if we only wanted to match those email addresses that have an actual underscore as their second character?
That’s where the escape character comes in.
SELECT * FROM Owners
WHERE Email NOT LIKE 'b\_%';Result:
+---------+-----------+----------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | +---------+-----------+----------+----------------+-------------------+ | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +---------+-----------+----------+----------------+-------------------+
We can see that Burt has been added to the results.
Changing the Escape Character
It’s possible to change the escape character. You can do this with the ESCAPE argument.
Example:
SELECT * FROM Owners
WHERE Email NOT LIKE 'b*_%' ESCAPE '*';Result:
+---------+-----------+----------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | +---------+-----------+----------+----------------+-------------------+ | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +---------+-----------+----------+----------------+-------------------+
In this case I assigned the asterisk (*) as the escape character.
This can be handy when you need to use the backslash for other purposes, such as encoding special characters. In such cases, you may find yourself having to “double-escape” these characters, which could become confusing. Changing the escape character can therefore help in such situations.