MySQL NOT LIKE Operator

In MySQL, NOT LIKE operator allows you to combine the NOT operator with the LIKE operator to perform pattern that does not match search specific pattern in a column.

The MySQL LIKE operator can be used with SELECT, INSERT, UPDATE and DELETE statement with the combination of WHERE clause. The percent (%) sign represents zero, one, or multiple characters and underscore (_) represents a single character.

Note: You can also combine any number of conditions with AND or OR operators.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

NOT LIKE Operator	Description
WHERE Address NOT LIKE 'c%'	Find field values that does not starts with "c"
WHERE Address NOT LIKE '%c'	Find field values does not ends with "c"
WHERE Address NOT LIKE '%and%'	Find field values that does not have "and" in any position
WHERE Address NOT LIKE '_2%'	Find field values that does not have "2" in the second position
WHERE Address NOT LIKE 'c%z'	Find field values that does not starts with "a" and ends with "z"

MySQL NOT LIKE OPERATOR Syntax

To search a record based on pattern matching use NOT LIKE operator, use the following syntax:

Syntax

SELECT col1, col2, ...
FROM tablename
WHERE colN NOT LIKE pattern;

Parameters:

tablename: The table name from which you want to fetch the records.

pattern: To perform pattern matching search in a column.

MySQL NOT LIKE OPERATOR example using % (percent) wildcard

The following MySQL statement is used to search a value based on pattern matching:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address NOT LIKE '12-13%';

Note:

In the above example, we are showing how to use the NOT LIKE operator to search a "address" that does not starting with '12-13'.

You can use MySQL Command Line Client to search a record based on pattern matching using NOT LIKE operator. It will look like this:

MySQL NOT LIKE OPERATOR example using % (percent) wildcard

The following MySQL statement is used to search a based on pattern matching:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address NOT LIKE '%Bhopal';

Note:

In the above example, we are showing how to use the LIKE operator to search a "address" that does not ending with 'Bhopal'.

You can use MySQL Command Line Client to search a record based on pattern matching using NOT LIKE operator. It will look like this:

MySQL NOT LIKE OPERATOR example using _ (underscore) wildcard

The following MySQL statement is used to search a record based on single character:

Example

SELECT firstName, lastName, address FROM tblCustomerLoan
WHERE address NOT LIKE '_2%';

Note:

In the above example, we are showing how to use the NOT LIKE operator using underscore (_) to search a address that does not have "2" in the second position with SELECT statement.

You can use MySQL Command Line Client to search a record based on pattern matching using NOT LIKE operator using underscore (_). It will look like this: