In Postgres, the LIKE operator performs the case-sensitive pattern matching on a specified string. So, if you want to perform the case-insensitive pattern matching on a string, then you can use the ILIKE operator instead of the LIKE operator.

In PostgreSQL, the ILIKE operator allows us to perform the case-insensitive pattern matching in SELECT, UPDATE, and DELETE statements. It can be used in the WHERE clause to filter the data based on case-insensitive pattern matching.

This blog explains how to perform case-insensitive pattern matching in postgres with suitable examples.

Case-Insensitive Pattern Matching in PostgreSQL Via ILIKE Condition/Operator

The syntax for using the ILIKE operator is presented in the below snippet:

targeted_string ILIKE pattern;

Two wildcards are used in Postgres to specify a particular pattern, i.e., an underscore “_” and a percent sign “%”. The underscore wildcard allows us to perform the pattern matching on a single character, while the percent sign is used to perform pattern matching on a sequence of characters.

Let’s learn this concept practically.

Example 1: How to Perform Case-insensitive Pattern Matching on a Single Column?

A sample table named staff_info has already been created:

Suppose we have to fetch all those employees whose names start with the letter “j”:

SELECT staff_name, staff_designation 
FROM staff_info
WHERE staff_name ILIKE 'j%';

The pattern “j%” represents that fetch all those strings that start with the letter “j” followed by anything:

We searched for the small “j”; however, the ILIKE operator retrieves the names irrespective of the lowercase. It proves that the ILIKE operator performs case-insensitive pattern matching.

Example 2: How to Perform Case-insensitive Pattern Matching on Multiple Columns?

Find all those employees whose name starts with “j” and whose designation is “author”:

SELECT staff_name, staff_designation 
FROM staff_info
WHERE staff_name ILIKE 'j%' AND staff_designation ILIKE 'author';

The above query retrieves all those authors whose name starts with “j”:

The output proved that the “ILIKE” operator performed the case-insensitive matching.

Example 3: How to Perform Case-insensitive Pattern Matching Using Underscore Wildcard?

All employees whose names contain "I" as a second letter will be listed using the following statement:

SELECT staff_name, staff_designation 
FROM staff_info
WHERE staff_name ILIKE '_I%';

The output shows that the ILIKE operator retrieves the records irrespective of the case.

Conclusion

In PostgreSQL, the ILIKE operator performs the case-insensitive pattern matching on a string. The ILIKE operator is often used in the WHERE clause to filter the data based on case-insensitive pattern matching. In Postgres, Two wildcards are used to specify a particular pattern in ILIKE operator, i.e., an underscore “_” and a percent sign “%”. This blog demonstrated a comprehensive guide on performing case-insensitive pattern matching in Postgres.