Using the MySQL NOT LIKE operator

TL;DR

The NOT LIKE operator in MySQL is used to filter records by excluding rows where a specific column matches a given pattern. It works in tandem with wildcard characters, much like the LIKE operator, but instead of finding matches, it looks for records that do not conform to the specified pattern.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name NOT LIKE pattern;

Here:

• column_name: The column you want to check.
• pattern: The string or pattern you want to exclude. Patterns often use wildcard characters such as:
  • %: Represents zero, one, or many characters.
  • _: Represents a single character.

Key Difference:

• The LIKE operator retrieves rows where the column matches a given pattern.
• The NOT LIKE operator retrieves rows where the column does not match the pattern.

How to Write a MySQL NOT LIKE Query

Writing a NOT LIKE query is straightforward once you understand the basic structure of LIKE. The trick lies in effectively using wildcard characters to define the patterns you want to exclude.

Example 1: Exclude Rows Based on a Prefix

Let’s say you have a table customers with a name column, and you want to find all customers whose names do not start with the letter “A.”

SELECT name
FROM customers
WHERE name NOT LIKE 'A%';

Explanation:

• The pattern 'A%' means any name that starts with “A.”
• NOT LIKE 'A%' excludes any names that start with “A.”

Example 2: Exclude Rows Based on a Suffix

You can also exclude rows based on a suffix. For example, to find products that do not have a .jpg extension in a files table:

SELECT file_name
FROM files
WHERE file_name NOT LIKE '%.jpg';

Here, the query excludes any file names that end with .jpg.

Example 3: Exclude Rows Based on a Specific Character Position

To exclude entries where the second character is the letter “e”, use the _ wildcard, which represents any single character:

SELECT name
FROM employees
WHERE name NOT LIKE '_e%';

This query excludes rows where the second letter in the name column is “e.” The _ wildcard ensures that the first character can be anything, while “e” must be the second character.

Practical Examples of MySQL NOT LIKE Queries

Let’s explore more examples that demonstrate different uses of NOT LIKE.

Example 4: Exclude Emails from a Specific Domain

Suppose you want to find users whose emails are not from the domain @gmail.com:

SELECT email
FROM users
WHERE email NOT LIKE '%@gmail.com';

In this query:

• '%@gmail.com' matches any email that ends with @gmail.com.
• NOT LIKE ensures that these emails are excluded from the results.

Example 5: Exclude Entries with a Specific Pattern in the Middle

To exclude names that contain “John” anywhere in the middle:

SELECT name
FROM employees
WHERE name NOT LIKE '%John%';

This query will return all rows where “John” does not appear in any part of the name.

Example 6: Exclude Certain Types of Products

Assume you have a products table with a category column, and you want to exclude categories like “Electronics” or “Furniture.”

SELECT product_name
FROM products
WHERE category NOT LIKE 'Electronics'
AND category NOT LIKE 'Furniture';

You can also use the IN operator for multiple exclusions:

SELECT product_name
FROM products
WHERE category NOT IN ('Electronics', 'Furniture');

While this version doesn’t use NOT LIKE, it achieves the same goal more succinctly when you need to exclude exact matches rather than patterns.

Conclusion

The NOT LIKE operator in MySQL is a powerful tool when you need to exclude data based on patterns. Whether you want to filter out certain prefixes, suffixes, or character combinations, NOT LIKE provides flexibility to create more refined queries.

By mastering both LIKE and NOT LIKE, you can handle a wide range of data filtering tasks in MySQL, improving both query efficiency and the precision of your results.

Summary of Key Points:

• The NOT LIKE query excludes rows that match a specific pattern.
• Wildcards like % and _ are crucial in defining patterns to exclude.
• Practical use cases include excluding specific prefixes, suffixes, or character combinations in strings.

Further Reading:

• MySQL Documentation: LIKE Operator
• MySQL Wildcard Characters

Understanding how to leverage MySQL’s NOT LIKE operator will allow you to refine your searches and make your queries more versatile.