PostgreSQL offers an “ILIKE” operator that is used to fetch/query the data based on pattern matching. The “ILIKE” operator works the same as the “LIKE” operator; the only difference is that the ILIKE operator queries the data irrespective of the letter case.
This Postgres blog will give you a detailed knowledge of the ILIKE operator with practical examples. So, let’s begin!
What Does ILIKE Operator Do in Postgres?
The ILIKE operator in Postgres performs case-insensitive pattern matching. For this purpose, Postgres offers a couple of wildcards denoted by a percent sign “%” and an underscore “_”:
- The percent wildcard “%” is used to match the sequences of characters.
- The underscore wildcard “_” is used to match only a single character.
- Both these wildcards can be used together to maximize functionality.
Syntax
The below syntax will be used to perform string matching using the ILIKE operator:
SELECT FROM tbl_name WHERE col_name ILIKE pattern;
In place of a pattern, you can specify any pattern of your choice using wildcards.
Example 1: How to Perform String Matching in Postgres Using Percent Wild Card?
Firstly, we will utilize the SELECT statement to fetch the details about the “bike_details” table:
SELECT * FROM bike_detials;
![img](/media/images/image_hZ5bqxp.width-1200.png)
From the bike_details table, we need to retrieve the bikes whose color starts with "bl" letter regardless of the letter case. To do so, we will utilize the ILIKE operator with the “%” wildcard as follows:
SELECT bike_model, bike_color FROM bike_details WHERE bike_color ILIKE 'bl%';
![img](/media/images/image_FsgL594.width-1200.png)
The output snippet proves that the “ILIKE” operator fetches the data irrespective of the letter case.
Example 2: How to Perform String Matching in Postgres Using Underscore Wildcard?
Suppose we need to check the bike number that starts with any letter/number, but the second letter must be a “y”:
SELECT bike_model, bike_number FROM bike_details WHERE bike_number ILIKE '_y%';
Here, in the above example:
- The underscore wildcard shows that the first letter can be anything, i.e., a number, character, etc.
- The second letter must be a “y”(irrespective of the case).
- The percent wildcard at the end shows that the “y” letter can be followed by any number/letter.
![img](/media/images/image_tpMilMc.width-1200.png)
The ILIKE operator fetched all those bike_numbers that contain a “y” letter at the second position.
Alright, folks! That’s all from this Postgres guide!
Conclusion
In PostgreSQL, the “ILIKE” operator is used to fetch/query the data based on pattern matching. The “ILIKE” operator works exactly the same as the “LIKE” operator; the only difference is that the ILIKE operator queries the data case-insensitively. In Postgres, the percent and underscore wildcards are used to create a pattern based on which the ILIKE operator queries the data from a string. This blog post has explained the working of the Postgres ILIKE operator via practical examples