MySQL NOT LIKE Operator

MySQL provides a way to find both types of records, either matched or not, with a specific value. This feature is advantageous when we want to find records that need to be edited or reported on a spreadsheet.

NOT LIKE operator in MySQL is used for pattern matching. It compares the column by the given value and returns the result that does not match the value in the NOT LIKE clause. If the statement finds the match, it will return 0. Otherwise, it will return 1. We can use this operator to perform the negation of the LIKE operator. The main advantage of this statement is to search for a range of values or values that match a pattern with the use of wildcards characters.

Syntax

The following is a basic syntax to use this operator in MySQL:

In this syntax, the expression is an input string on which we will perform searching for matching the regular expression. And pattern represents the regular expression for which we are testing the string. The optional ESCAPE clause is used to specify an escape character. If we do not specify this clause, by default, it is \.

The above operator is equivalent to the below syntax:

This syntax is generally used with the SELECT statements as follows:

Let us understand how this operator works in MySQL through examples.

Example

The below statement is the most basic example of a NOT LIKE operator. Here we have just used a string and compare if any part of the input string matches the pattern to get the result.

Here is the result:

If we want to escape any of the wildcard characters (_ and %), we need to use the backslash character (\). See the below statement where will do a search with and without the escape character:

Here is the result:

Suppose we have a table named student_info that contains the following data. We will demonstrate various examples based on this table data.

If we want to get the students detail whose name does not start with the 'A' letter, we can use the statement as follows:

Executing the statement, we will get the desired result. See the below output:

The below MySQL statement returns those rows from the table student_info that have the subject name like the pattern specified with the LIKE operator:

We will get the below output where we can see the statement does not return the records that contain the above patterns.

If we want to get the student detail whose subject name does not end with 's' character, we can use the below statement to do this:

After executing this statement, we will get the desired result:

The below example shows how to use the NOT LIKE operator with the numeric expressions:

Here is the result: