How to Use ILIKE in Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
WHERE column_name ILIKE 'a%'

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

1
WHERE column_name ILIKE '%a'

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

1
WHERE column_name ILIKE '%a%'

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

1
WHERE column_name ILIKE '_a%'

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

1
WHERE column_name ILIKE 'a%b'

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:

1
CREATE DATABASE demo;
  • 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:

1
2
3
4
5
CREATE TABLE student(
    id SERIAL PRIMARY KEY,
    name TEXT,
    gender VARCHAR(6)
);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |      name       | gender
----+-----------------+--------
  1 | Paulina Bright  | Female
  2 | Eliza Herring   | Female
  3 | Darren Ferguson | Male
  4 | Artur Cisneros  | Male
  5 | Brittany Hodges | Female
  6 | Joe Green       | Male
  7 | George Welch    | Male
  8 | Anthony Mann    | Male
  9 | Michele Morgan  | Female
 10 | Alycia Seaton   | Female
(10 rows)

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:

1
2
3
4
5
6
7
SELECT * FROM student WHERE name ILIKE 'a%';
 id |      name      | gender
----+----------------+--------
  4 | Artur Cisneros | Male
  8 | Anthony Mann   | Male
 10 | Alycia Seaton  | Female
(3 ROWS)

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:

1
2
3
4
5
6
 SELECT * FROM student WHERE name ILIKE '__i%';
 id |      name       | gender
----+-----------------+--------
  2 | Eliza Herring   | Female
  5 | Brittany Hodges | Female
(2 ROWS)

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:

1
2
3
4
5
6
SELECT * FROM student WHERE name NOT ILIKE '%O%';
 id |      name      | gender
----+----------------+--------
  1 | Paulina Bright | Female
  2 | Eliza Herring  | Female
(2 ROWS)

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:

1
2
3
4
5
SELECT * FROM student WHERE name ILIKE '% G%';
 id |   name    | gender
----+-----------+--------
  6 | Joe Green | Male
(1 ROW)

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

1
2
3
4
5
6
7
8
9
10
 SELECT * FROM student WHERE name ILIKE '% %r%';
 id |      name       | gender
----+-----------------+--------
  1 | Paulina Bright  | Female
  2 | Eliza Herring   | Female
  3 | Darren Ferguson | Male
  4 | Artur Cisneros  | Male
  6 | Joe Green       | Male
  9 | Michele Morgan  | Female
(6 ROWS)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.