How to Use ILIKE in Postgres

Introduction

If you’ve done any pattern matching in your PostgreSQL queries, you’ve probably become acquainted with the LIKE operator. However, you might not yet be familiar with its counterpart: the ILIKE operator. For the most part, this operator behaves in a similar manner to the LIKE operator; what makes it unique is that it allows for case-insensitive pattern matching. In this article, we’ll take a closer look at the ILIKE Postgres operator and check out some examples of how it can be used in queries.

Prerequisites

Before diving into the examples in this tutorial, take a moment to review the prerequisites for the task:

• Be sure to have PostgreSQL installed on your computer so that you can follow along with our instructions and examples.
• It’s helpful to have at least some knowledge of PostgreSQL in order to get the most out of this tutorial.

PostgreSQL ILIKE Operator Syntax

As we mentioned earlier, the ILIKE operator is used in the same way as the LIKE operator. The difference is that ILIKE allows you to perform case-insensitive pattern matching. Shown below is the basic syntax for this operator:

In the syntax example above, we search for the values that start with “a”:

This example searches for the values that end with “a”:

We can even search for the values that contain “a” in any position:

The above example looks for values that contain “a” in the second position:

Our final syntax example in this section searches for the values that start with “a” and end with “b”.

Create a PostgreSQL database and table

Before we can show you some real-life examples of queries using ILIKE in Postgres, we’l need to set up a database and table. If you already have these available for use, you can skip this section; otherwise, follow along with the steps listed below.

To create a database in PostrgreSQL, we use the following statement:

• We can use the command \c to connect our database once it’s created:

To create a table in PostgreSQL, we’ll use this statement:

Shown below is the contents of our table. You can populate your table with the same records or use different data if you prefer:

PostgreSQL ILIKE Examples

Searching using ILIKE

In our first example using our student table, we will search for names that start with “a”. We will use the following statement:

You can see that the records in the result do contain names that match the query conditions.

Searching for specific character position

Our next example will return records that have “i” as the third character of the string:

Using NOT ILIKE

Let’s look at an example that searches for records that do not contain the letter “o”. To do this, we’ll use the NOT ILIKE expression as seen in the following statement:

Searching for the after-space character

If the name field contains both first and last names together, how could we search for the first character of a student’s last name? We could use the ILIKE operator to locate the first character that comes after the space that separates first and last name:

In this example, we search for the letter “r” in any position in the last name:

Conclusion

When you need to perform pattern matching in PostgreSQL but also need to ignore case, the ILIKE operator provides the functionality you need. In this article, we discussed the ILIKE Postgres operator and showed many examples of its use. If you’ve followed along with this tutorial, you’ll be prepared to incorporate the ILIKE operator into your own PostgreSQL queries.