SQL NOT LIKE with PostgreSQL

Introduction

Much of the data we store in our databases is text and we need advanced ways of filtering it for the exact data we need. Filtering data can become very complex and there are several ways PostgreSQL enables you to search for it. PostgreSQL in particular offers three ways to do pattern matching, LIKE, SIMILAR, and regular expressions. In this article we’ll talk about the SQL NOT LIKE operator. Although there are big differences between the SQL databases we’ll be using PostgreSQL to illustrate what it does.

First the LIKE operator

First we’ll discuss the LIKE operator since the NOT LIKE operator is the opposite of that operator. The LIKE operator takes in a pattern and returns true if the string matches that pattern.

There are two wildcards that it uses for pattern matching:

• % – Zero, one, or multiple characters
• _ – Represents a single character

Let’s quickly look at an example of how these wildcards are used:

In the first example the two strings match exactly so it returns true. In the second example the h matches then the % operator matches the remaining ello part of the string, so the pattern was a match. In the third example the _ underscore character matched the h and the o at the end which allowed the match. The last example did not match because x was nowhere in the string.

The NOT LIKE operator

Now that you understand the LIKE operator let’s look at NOT LIKE. NOT LIKE is the exact opposite of the LIKE OPERATOR. It will return true for any string that doesn’t match the pattern. So the same examples would return the exact opposite.

It is just the negation of the LIKE operator.

When would you use the NOT LIKE operator?

Sometimes it’s more succinct to specify the data that you don’t want rather than the data you do. For example if you had a database for a small grocery store and one of your tables contained all products you have.

You may want to get all the products that are not in the ‘Dairy’ department. You could accomplish that with the query:

But it would be much more readable using the NOT LIKE operator like so:

Conclusion

We have gone through how to use the SQL NOT LIKE operator with PostgreSQL. We hope you were able to follow along and get the information you needed to solve your issue.

If you weren’t able to solve your issue and would like some suggestions or want to pass off the management of your database so you can focus on other areas of your application, please don’t hesitate to reach out to us at Object Rocket. We’re happy to discuss your application and your needs.