What is NOT LIKE operator in SQL?

In SQL, the NOT LIKE operator is used in conjunction with the WHERE clause to filter the results returned by a query based on patterns that do not match the specified criterion. Essentially, it is the negation of the LIKE operator, which is used to find rows that match a specified pattern. The NOT LIKE operator is particularly useful when you need to exclude rows that contain a certain pattern in a column.

Syntax

The syntax for using the NOT LIKE operator in SQL is:

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

Pattern Matching with NOT LIKE

The pattern typically includes wildcards such as % or _:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Examples

Here are a few examples to illustrate how the NOT LIKE operator works:

1. Exclude Entries Containing a Specific String Suppose you have a customers table and you want to find all customers whose names do not contain the substring "Smith".

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

   This query returns all names that do not have "Smith" anywhere in the name field.

2. Exclude Entries that Start with a Specific Character If you need to retrieve all products in a products table that do not start with the letter 'A'.

   SELECT product_name
   FROM products
   WHERE product_name NOT LIKE 'A%';

   This query filters out all product names starting with 'A' and includes all others.

3. Exclude Entries that End with a Specific Sequence To get all employees from an employees table whose email addresses do not end with "@example.com":

   SELECT email
   FROM employees
   WHERE email NOT LIKE '%@example.com';

   This query will list all email addresses that do not end with "@example.com".

4. Exclude Entries Matching Exact Character Positions You might want to exclude records where the second letter of a code column is 'X':

   SELECT code
   FROM codes
   WHERE code NOT LIKE '_X%';

   This ensures that any codes where 'X' is the second character are not included in the results.

Use Cases and Considerations

• The NOT LIKE operator is invaluable for filtering out unwanted patterns, especially when dealing with textual data.
• It is important to consider the use of indexes when using LIKE or NOT LIKE. These operators can sometimes lead to full table scans if not used carefully, particularly when the wildcard % is placed at the beginning of the pattern.
• For case sensitivity, the behavior can depend on the collation settings of the database or column. In some SQL databases, like PostgreSQL, the LIKE and NOT LIKE operators are case-sensitive, while in others, such as SQL Server or MySQL, this can depend on the collation configured.

Using NOT LIKE effectively can help manage and analyze data by excluding specific patterns that are not relevant to the analytical requirements or by filtering out potential outliers or errors in data entries.