NOT LIKE

In the previous section, we discussed and saw examples of using the LIKE operator and how the use of wildcards that provide for robust filtering based on patterns. In this section, we look at an example of NOT LIKE. We know by now the NOT operators simply returns all records that evaluated to false in a given condition.

Let's demonstrate an example of NOT LIKE by filtering for which customers are from countries with very short names (3 characters or less). The query to implement this looks like below:

select *
from customers
where country NOT like '____%'

| customer_id | first_name | last_name | email                   | city     | country | registration_date | credit_limit |
| ----------- | ---------- | --------- | ----------------------- | -------- | ------- | ----------------- | ------------ |
| 2001        | Alice      | Johnson   | [email protected] | New York | USA     | 2023-01-15        | 5000         |
| 2002        | Bob        | Williams  | [email protected]  | London   | UK      | 2023-02-20        | 7500         |

As we expect, the output will only contain customers that have country names with 3 or less characters.

Let's demonstrate an example of NOT LIKE by filtering for which customers are from countries with very long names (6 characters or less). The query to implement this looks like below:

select *
from customers
where country NOT like '_____%'

| customer_id | first_name | last_name | email                      | city      | country | registration_date | credit_limit |
| ----------- | ---------- | --------- | -------------------------- | --------- | ------- | ----------------- | ------------ |
| 2001        | Alice      | Johnson   | [email protected]    | New York  | USA     | 2023-01-15        | 5000         |
| 2002        | Bob        | Williams  | [email protected]     | London    | UK      | 2023-02-20        | 7500         |
| 2007        | Grace      | Moore     | [email protected]      | Tokyo     | Japan   | 2023-07-22        | 9000         |
| 2008        | Henry      | Taylor    | [email protected]     | Madrid    | Spain   | 2023-08-30        | 5500         |
| 2009        | Ivy        | Anderson  | [email protected]     | Rome      | Italy   | 2023-09-14        | 7000         |
| 2013        | Mia        | Garcia    | [email protected]       | Barcelona | Spain   | 2023-01-28        | 5200         |
| 2019        | Sam        | Wang      | [email protected]         | Shanghai  | China   | 2023-07-15        | 6200         |
| 2020        | Tina       | Liu       | [email protected]         | Beijing   | China   | 2023-08-20        | 5800         |
| 2021        | Uma        | Patel     | [email protected]        | Mumbai    | India   | 2023-09-10        | 4200         |
| 2022        | Victor     | Singh     | [email protected]     | Delhi     | India   | 2023-10-15        | 3600         |
| 2023        | Wendy      | Kumar     | [email protected]      | Bangalore | India   | 2023-11-20        | 5400         |
| 2024        | Xavier     | Gupta     | [email protected]     | Kolkata   | India   | 2023-12-12        | 4800         |
| 2025        | Yuki       | Tanaka    | [email protected]      | Osaka     | Japan   | 2023-01-05        | 7600         |
| 2026        | Zoe        | Yamamoto  | [email protected]     | Kyoto     | Japan   | 2023-02-10        | 8200         |
| 2029        | Carlos     | Fernandez | [email protected] | Madrid    | Spain   | 2023-05-25        | 4700         |
| 2030        | Diana      | Rossi     | [email protected]      | Milan     | Italy   | 2023-06-30        | 7800         |

The difference here is that we are saying we want to eliminate any customer_id with two or more digits. This will effectively always return single digits ids.

This concludes our discussion on the NOT LIKE operator.