Skip to content

NOT LIKE

Grammar Description

The NOT LIKE operator is used to search for specified patterns in columns in the WHERE clause and is a negative usage of LIKE.

There are two wildcards that are often used with the LIKE operator:

  • Percent sign (%) represents 0, 1 or more characters.
  • Underscore (_) represents a single character.

Grammar Structure

> SELECT column1, column2, ...
FROM table_name
WHERE columnN NOT LIKE pattern;

Example

create table t1 (a char(10));
insert into t1 values('abcdef');
insert into t1 values('_bcdef');
insert into t1 values('a_cdef');
insert into t1 values('ab_def');
insert into t1 values('abc_ef');
insert into t1 values('abcd_f');
insert into t1 values('abcde_');

mysql> select * from t1 where a not like 'a%';
+---------+
| a |
+---------+
| _bcdef |
+---------+
mysql> select * from t1 where a not like "%d_\_";
+---------+
| a |
+---------+
| abcdef |
| _bcdef |
| a_cdef |
| ab_def |
| abc_ef |
| abcd_f |
+---------+
6 rows in set (0.01 sec)