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:
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