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;

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%';

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.

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