PostgreSQL – NOT LIKE operator

Last Updated : 11 Oct, 2024

The PostgreSQL NOT LIKE operator is a powerful tool used in SQL queries to filter out rows that do not match specific patterns. By utilizing this operator, users can eliminate undesired string patterns from their results, enabling more precise data retrieval.

It is particularly beneficial in scenarios where specific naming conventions or formats need to be excluded. In this article, We will learn about the PostgreSQL NOT LIKEOperator by understanding various examples and so on.

PostgreSQL NOT LIKE Operator

The NOT LIKE operator is used in SQL queries to filter out rows that do not match a specific pattern. It is especially useful when we need to eliminate certain string patterns from our results.
This operator is part of PostgreSQLs string comparison toolkit and can work with other PostgreSQL window functions or conditional operators.

Syntax:

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

Explanation:

column_name: The column to filter.
pattern: The string pattern, with wildcards (% for multiple characters and _ for a single character) to match against.

Examples of NOT LIKE operator

Example 1:

Let’s write an query is to retrieve the first and last names of customers from the customer table whose first names do not start with the letter ‘K’.

SELECT
    first_name,
        last_name
FROM
    customer
WHERE
    first_name NOT LIKE 'K%';

Output:

Explanation:

In this SQL query, we use the SELECTstatement to specify the columns first_name and last_name we want to retrieve from the customer table.
The WHERE clause filters the results to exclude any records where the first_name begins with ‘K’, indicated by the NOT LIKE 'K%' condition.
The percentage symbol (%) is a wildcard in SQL that represents zero or more characters, meaning any first name starting with ‘K’ will not be included in the result set.

Example 2:

Let’s write an query is to retrieve the first and last names of customers from the customertable whose first names do not contain the substring “her” at the second position.

SELECT
    first_name,
    last_name
FROM
    customer
WHERE
    first_name NOT LIKE '_her%';

Output:

Explanation:

This SQL query retrieves the `first_name` and `last_name` of customers from the `customer` table whose first names do not have the substring “her” starting from the second position.
The `NOT LIKE ‘_her%’` condition uses the underscore (`_`) wildcard to represent any single character before “her,” ensuring that only names that do not fit this pattern are returned.

Conclusion

In summary, the NOT LIKE operator in PostgreSQL provides an effective means to refine query results by excluding unwanted patterns. Whether you need to filter out names based on their initial letters or exclude specific substrings, this operator simplifies the process.

FAQs

What is the difference between the LIKE and NOT LIKE operators in PostgreSQL?

The LIKE operator retrieves rows that match a specified pattern, while the NOT LIKE operator filters out those that match, returning only the rows that do not conform to the given pattern.

Can I use multiple patterns with the NOT LIKE operator in PostgreSQL?

Yes, you can combine multiple NOT LIKE conditions using the AND operator to filter out rows that match any of the specified patterns.

What wildcards can I use with the NOT LIKE operator in PostgreSQL?

In PostgreSQL, you can use the percentage sign (%) as a wildcard for multiple characters and the underscore (_) as a wildcard for a single character when using the NOT LIKE operator.