What is NOT LIKE operator in SQL?

Key takeaways:

• The NOT LIKE operator excludes rows from query results based on specific patterns.

• Wildcards % and _ allow flexible pattern matching in the NOT LIKE operations.

• NOT LIKE is case-insensitive by default, but collation settings can affect this behavior.

• Precise pattern matching can be achieved using _ for single characters and % for multiple characters.

• NOT LIKE can effectively filter out data that starts or ends with certain patterns or exclude entries with specific characteristics.

Assuming the goal is to filter products from a database for the upcoming summer sale on an e-commerce platform, the task was to highlight items ideal for the summer season while excluding those with winter-related attributes or other irrelevant keywords. These winter-related attributes might include terms like “Winter,” “Snow,” “Thermal,” or “Coat.” To ensure the summer sale featured only the most relevant products, it was essential to exclude these winter-related entries. To fulfill this requirement, SQL provides a powerful tool: the NOT LIKE operator.

SQL’s NOT LIKE operator

Donald D. Chamberlin and Raymond F. Boyce designed SQL, which includes a range of filtering operators to refine query results. Among these operators, the NOT LIKE operator excludes rows based on specific patterns, enhancing the ability to filter data effectively. By defining a pattern with wildcards, we can exclude records that match certain criteria, effectively narrowing down the dataset to only those entries that do not conform to the unwanted pattern.

Syntax

The following is the basic syntax for using SQL’s NOT LIKE operator.

• SELECT column1, column2, ...: Specifies the columns to retrieve.

• FROM table_name: Indicates the table from which to retrieve data.

• WHERE column_name NOT LIKE pattern: Filters rows where the column_name does not match the pattern. Here pattern can be defined using the % and _ wildcards.

Let’s use code examples to understand the NOT LIKE operator functionality with wildcards for different scenarios.

1. Use NOT LIKE with the % wildcard character

The % is a wildcard character that matches a string's zero, one, or more characters. For example, if we want to filter names that do not start with 'A', we will first apply % wildcard to search for names starting with 'A' letter and then apply NOT LIKE operator. This will return filtered data containing only names other than names starting with "A".

2. Use NOT LIKE with the _ wildcard character

The underscore _ is used when we want to match a single character at a specific position in a string. It’s useful for more precise pattern matching. Let’s see the following illustration to understand the _ wildcard character using the example.

As an example, suppose that we have the following Demo table, as follows:

The following code example first extracts elements of length 3 that end with 't'. Next, NOT LIKE uses this result to filter them from the whole table and returns those inputs that are not length 3 and do not end in 't'.

A common example of using the NOT LIKE operation with _ wildcard that filters the phone numbers involves excluding entries that do not start with a specific prefix followed by a fixed number of characters. For example, suppose we want to exclude all phone numbers that start with the area code (124) followed by exactly 8 more digits ((124) ________).

In conclusion, the NOT LIKE operator in SQL is a great tool for filtering out records that match specific patterns. By leveraging wildcards such as % and _, we can effectively exclude data that meets unwanted criteria. It is useful in scenarios where we need to exclude entries based on complex patterns or conditions.