How NOT LIKE Works in MySQL

Posted on by Ian

In MySQL, you can use NOT LIKE to perform a negation of the LIKE operator. In other words, NOT LIKE returns the opposite result to LIKE.

If the string matches the pattern provided, the result is 0, otherwise it’s 1.

The pattern doesn’t necessarily need to be a literal string. This function can be used with string expressions and table columns.

Syntax

The syntax goes like this:

expr NOT LIKE pat [ESCAPE 'escape_char']

Where expr is the input string and pat is the pattern for which you’re testing the string against.

The optional ESCAPE clause allows you to specify an escape character. The default escape character is \, so you can omit this clause if you don’t need to change this.

This operator is the equivalent of doing the following: 

NOT (expr LIKE pat [ESCAPE 'escape_char'])

Example 1 – Basic Usage

Here’s an example of how to use this operator in a SELECT statement:

SELECT 'Charlie' NOT LIKE 'Char%';

Result:

+----------------------------+
| 'Charlie' NOT LIKE 'Char%' |
+----------------------------+
|                          0 |
+----------------------------+

In this case, the return value is 0 which means that the input string did actually match the pattern.

Example 2 – Compared to LIKE

Here it is compared to LIKE:

SELECT 
  'Charlie' LIKE 'Char%' AS 'Like',
  'Charlie' NOT LIKE 'Char%' AS 'Not Like';

Result:

+------+----------+
| Like | Not Like |
+------+----------+
|    1 |        0 |
+------+----------+

Example 3 – An Equivalent Syntax

As mentioned, NOT LIKE is the equivalent of using the NOT logical operator against the LIKE operator. Here’s what I mean:

SELECT 
  'Charlie' NOT LIKE 'Char%' AS 'NOT LIKE syntax',
  NOT ('Charlie' LIKE 'Char%') AS 'Equivalent syntax';

Result:

+-----------------+-------------------+
| NOT LIKE syntax | Equivalent syntax |
+-----------------+-------------------+
|               0 |                 0 |
+-----------------+-------------------+

Example 4 – A Database Example

The LIKE operator is often used within a WHERE clause of a SELECT statement when querying a database. Therefore, NOT LIKE can be used in the same fashion.

When we use NOT LIKE in this fashion, it narrows down the results to only those records that don’t match, but we see the actual results (not just a 1 or 0).

Here’s an example of how we can use this operator within a database query:

SELECT ArtistId, ArtistName
FROM Artists
WHERE ArtistName NOT LIKE 'B%';

Result:

+----------+------------------------+
| ArtistId | ArtistName             |
+----------+------------------------+
|        1 | Iron Maiden            |
|        2 | AC/DC                  |
|        3 | Allan Holdsworth       |
|        5 | Devin Townsend         |
|        6 | Jim Reeves             |
|        7 | Tom Jones              |
|        8 | Maroon 5               |
|        9 | The Script             |
|       10 | Lit                    |
|       12 | Michael Learns to Rock |
|       13 | Carabao                |
|       14 | Karnivool              |
+----------+------------------------+

In this case, it was a simple query that returns all artists whose names don’t start with the letter B.

Here’s the full list of artists in that table:

SELECT ArtistId, ArtistName
FROM Artists;

Result:

+----------+------------------------+
| ArtistId | ArtistName             |
+----------+------------------------+
|        1 | Iron Maiden            |
|        2 | AC/DC                  |
|        3 | Allan Holdsworth       |
|        4 | Buddy Rich             |
|        5 | Devin Townsend         |
|        6 | Jim Reeves             |
|        7 | Tom Jones              |
|        8 | Maroon 5               |
|        9 | The Script             |
|       10 | Lit                    |
|       11 | Black Sabbath          |
|       12 | Michael Learns to Rock |
|       13 | Carabao                |
|       14 | Karnivool              |
|       15 | Birds of Tokyo         |
|       16 | Bodyjar                |
+----------+------------------------+

So if we remove the NOT (i.e. we just use LIKE) we get this result:

SELECT ArtistId, ArtistName
FROM Artists
WHERE ArtistName LIKE 'B%';

Result:

+----------+----------------+
| ArtistId | ArtistName     |
+----------+----------------+
|        4 | Buddy Rich     |
|       11 | Black Sabbath  |
|       15 | Birds of Tokyo |
|       16 | Bodyjar        |
+----------+----------------+

Example 5 – Escaping with the Backslash Character

The backslash character (\) can be used to escape any of the wildcard characters (_ and %). Here’s an example of such a search with and without the escape character:

SELECT 
  'usr+123' NOT LIKE 'usr_123' AS 'Without escape',
  'usr+123' NOT LIKE 'usr\_123' AS 'With escape';

Result:

+----------------+-------------+
| Without escape | With escape |
+----------------+-------------+
|              0 |           1 |
+----------------+-------------+

Example 6 – The ESCAPE Clause

You can also use the ESCAPE clause to specify your own custom escape character. Here’s an example:

SELECT 
  'usr_123' NOT LIKE 'usr|_123' ESCAPE '|' AS 'String 1',
  'usr+123' NOT LIKE 'usr|_123' ESCAPE '|' AS 'String 2';

Result:

+----------+----------+
| String 1 | String 2 |
+----------+----------+
|        0 |        1 |
+----------+----------+

Example 7 – Numeric Expressions

This operator can be used on numeric expressions. Here’s an example:

SELECT 
  1234 NOT LIKE '12%',
  1234 NOT LIKE '12_';

Result:

+---------------------+---------------------+
| 1234 NOT LIKE '12%' | 1234 NOT LIKE '12_' |
+---------------------+---------------------+
|                   0 |                   1 |
+---------------------+---------------------+