MariaDB NOT LIKE Operator

In MariaDB, the NOT LIKE operator is negation operation of the LIKE operator.

Note that the MariaDB NOT LIKE operator performs whole string matches, not partial matches like regular expressions.

MariaDB NOT LIKE Syntax

The NOT LIKE operator is a binary comparison operator that takes two operands. The syntax is as follows:

expression NOT LIKE pattern [ESCAPE 'escape_char']

Here:

  • expression can be a column name, value, or expression (such as function calls).
  • pattern is a text pattern. You can use two wildcards (% and _) in this pattern.
    • % matches zero or more arbitrary characters.
    • _ matches any single character.
  • If you need to match a wildcard, you need to use the escape character \, such as \% and \_.
  • The pattern is not case-sensitive.

The NOT LIKE operator returns 0 if expression matches pattern, otherwise 1.

For example:

  • a% matches any string starts with a, such as a, ab, abc.
  • %a matches any string ends with a, such as a, ba, nba.
  • %a% matches any string includes a, such as a, abc, nba, bac.
  • %a%b% matches any string includes a, and a is ahead of b, such as ab, 1a1b1.
  • a_ matches any string starts a and the length of string is 2.
  • _a matches any string end a and the length of string is 2.

MariaDB NOT LIKE Examples

%

% matches zero or more arbitrary characters, the following statement demonstrates the usage of the % wildcard:

SELECT
  'abc' LIKE 'a%',
  'abc' NOT LIKE 'a%';

Output:

+-----------------+---------------------+
| 'abc' LIKE 'a%' | 'abc' NOT LIKE 'a%' |
+-----------------+---------------------+
|               1 |                   0 |
+-----------------+---------------------+

Here, the a% pattern represents a string of any length beginning with a, and thus it matches abc.

Another one:

SELECT
  'abc' LIKE '%b%',
  'abc' NOT LIKE '%b%';

Output:

+------------------+----------------------+
| 'abc' LIKE '%b%' | 'abc' NOT LIKE '%b%' |
+------------------+----------------------+
|                1 |                    0 |
+------------------+----------------------+

Here, the %b% pattern represents a string includes b and thus it matches abc.

_

_ matches a arbitrary character, the following statement demonstrates the use of the _ wildcard:

SELECT
  'ab' LIKE 'a_',
  'ab' NOT LIKE 'a_',
  'ab' LIKE '_b',
  'ab' NOT LIKE '_b';

Output:

+----------------+--------------------+----------------+--------------------+
| 'ab' LIKE 'a_' | 'ab' NOT LIKE 'a_' | 'ab' LIKE '_b' | 'ab' NOT LIKE '_b' |
+----------------+--------------------+----------------+--------------------+
|              1 |                  0 |              1 |                  0 |
+----------------+--------------------+----------------+--------------------+

database example

The following example uses the actor table from the Sakila sample database.

SELECT * FROM actor
WHERE first_name NOT LIKE 'P%'
LIMIT 10;

Output:

+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
|       11 | ZERO       | CAGE         | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+

Conclusion

In MariaDB, the NOT LIKE operator is negation operation of the LIKE operator.