This guide will look at the ILIKE operator available in PostgreSQL.
Prerequisites
To perform the steps demonstrated in this guide, you will need the following components:
- A properly-configured Linux system. For testing purposes, I’m running Ubuntu 22.04 on VirtualBox. Learn more about installing Ubuntu on VirtualBox.
- The latest version of PostgreSQL is installed. This guide demonstrates installing PostgreSQL on Debian/Ubuntu.
For ease of use, I’m also using phpPgAdmin, a web-based front-end for PostgreSQL (similar to phpMyAdmin). It’s directly available in the official Ubuntu package repos.
Note that phpPgAdmin will automatically install and configure the Apache2 server to serve the web front-end.
In addition, I also borrowed this sample DVD Rental database from PostgreSQL Tutorial.
PostgreSQL ILIKE Operator
Suppose you’re searching for information that matches a specific pattern. In PostgreSQL, you can utilize a couple of options: the LIKE operator and ILIKE operator.
For the most part, the ILIKE operator behaves similarly to the LIKE operator. However, the main distinguishing feature is the case sensitivity. The ILIKE operator performs case-insensitive pattern matching. In many situations, case-insensitive searches are preferable to case-sensitive searches.
The ILIKE operator supports two special wildcard characters:
- Percentage (%): matches for any sequence of characters.
- Underscore (_): matches for any single character.
The structure is as follows:
Using the ILIKE Operator
As mentioned earlier, both the LIKE and ILIKE operators act the same except for case sensitivity. Learn more about using the LIKE operator in PostgreSQL.
Example 1
In the following example, we will perform a simple search on the contents of the table “customers” (first_name):
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE 'Al%';
Here, we defined the pattern to search for is “Al%”, meaning any number of characters can appear after “Al”.
Example 2
What if we wanted to search for first_name entries that end with some specific characters? In that case, the code would look like this:
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE '%e';
Here, the ILIKE operator matches all the entries that end with the character “e” at the end.
Example 3
In the next example, ILIKE matches any entry with the character “x” in any position:
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE '%x%';
Example 4
Let’s try combining both of the wildcard symbols:
first_name,
last_name
FROM
customer
WHERE
first_name ILIKE '_oR%';
Here, the pattern “_oR%” describes that any single character is at the start, then must be followed by “oR”, and can have any number of characters at the end.
Example 5
What if we wanted the matching entries to be excluded from the result? Then, we would like to use NOT ILIKE.
In the following example, the result will exclude any result matching the pattern “%e”:
first_name,
last_name
FROM
customer
WHERE
first_name NOT ILIKE '%e';
Conclusion
This article discusses the ILIKE operator in PostgreSQL with multiple examples. It provides the ability to perform pattern matching while ignoring cases. After following this guide, you’ll be prepared to implement ILIKE operators in your own PostgreSQL queries.
Interested in learning more about PostgreSQL? Check out the PostgreSQL sub-category. Learn more about copying tables from one database to another, using the FLOOR function, working with arrays, etc.
