How to use ilike in PostgreSQL

In this PostgreSQL tutorial, I will show how to use ILIKE in PostgreSQL. You will learn about using the ILIKE case-insensitive with columns in the table. Also, learn how to use the wildcards like underscore (_) and percent symbol (%) with ILIKE case-insensitive.

How to use ilike in PostgreSQL

Let’s discuss all possible cases, along with relevant examples.

Approach-1: Simple Case-Insensitive Matching

To match the pattern on a given string in a case-insensitive way, use the operator ILIKE. So, “What is a pattern?”. The pattern is a collection of strings; When this pattern matches with a string, if the pattern exists in that string, then the ILIKE operator returns ‘true’; otherwise, it returns ‘false’.

The syntax is given below.

source_string ILIKE pattern_to_match

• source_string: It is a string that may contain the pattern.
• ILIKE: The operator makes case-insensitive pattern matching.
• pattern_to_match: The exact pattern that you want to match with the source string.

Let’s use the following example with the command below.

SELECT 'United States' ILIKE 'united states';

After executing the above query, I got the expected output as shown in the screenshot below.

In the above output, the pattern ‘united states’ exists in the source string ‘United States’ so the output is True.

Now you have matched the pattern with the entire string. Suppose you have a pattern that contains a single string or some string, and you want to make the pattern match the source string. If the pattern exists in the source string, then it should return ‘true’, otherwise ‘false’.

PostgreSQL provides two symbols, underscore (_) and percent sign (%); the underscore is used for single characters, and the percent sign is for some characters.

Take an example using the underscore and percent sign, as shown in the commands below.

SELECT 'United States' ILIKE '_nited states';

After executing the above query, I got the expected output as shown in the screenshot below.

If you examine the above query in the pattern ‘_nited states’, the underscore (_) symbol is used, indicating that the first character of the pattern string is unknown, but the latter characters are known. So find these strings within the source string ‘United Stated’.

As in the output, you can see the returned value is ‘true’, which means the pattern ‘_united states’ exists in the source string ‘United States’. So you can use the underscore (_) anywhere in the pattern like before (_nited states’), after (united state_), and middle (unite_ states).

Next, we will see how to use the percent sign (%) for several characters. Suppose you don’t remember the word ‘united’ within the string ‘United States’ and you only know about the word ‘states’, but you know that the word that you don’t remember comes before the word ‘states’.

You can use the below query to find matches for the whole string.

SELECT 'United States' ILIKE '%states';

After executing the above query, I got the expected output as shown in the screenshot below.

If you look at the pattern ‘%states’ in the above query, this pattern tells that the characters before the word ‘states’ is unknown. Find this pattern within the string ‘United States’. As ILIKE finds the word ‘states’ within the source string ‘United States’, it returns the value ‘true’.

You can also use this symbol before (%states), middle (unit% states), and after (united%) the word within the pattern.

Approach-2: On a Single Column

You can make pattern matching on a single column of the table. Suppose you have a table called ’employees’ with columns ’employee_id’, ‘name’, ‘department_id’, and ‘salary’. You want to find the names of all employees whose names contain the letter ‘e’.

SELECT name From employees
WHERE name ILIKE '%e%';

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the pattern ‘%e%’, this means the characters or words before and after the letter ‘e’ are unknown, and find the names of employees whose name contains only the letter ‘e’. There are two employees, Alice Johnson and Charlie Brown, whose names contain the letter ‘e’.

Approach-3: On Multiple Columns

You can perform pattern matching on multiple columns using the ILIKE case-insensitive operator. For example, use the same table ’employees’. Suppose you want to find the name of an employee who contains the letter ‘e’ with a salary greater than $52000.

SELECT name,salary From employees
WHERE name ILIKE '%e%' and salary > 52000;

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the above query, the two columns ‘name’ and ‘salary’ are used with the ‘ILIKE’ operator and connected using the ‘and’ operator. As you can see, only one employee, ‘Charlie Brown’, has a salary of $55000, which is greater than $52000.

Approach-4: Using Underscores for Single Character Matching

The underscore wildcard (_) is particularly useful when you know exactly how many characters you’re looking for. The example below will find all three-letter state abbreviations that end with ‘A’.

SELECT DISTINCT first_name 
FROM customers 
WHERE first_name ILIKE '__A';

After executing the above query, I got the expected output as shown in the screenshot below.

Method 5: Combining Multiple ILIKE Conditions

We can also use multiple ILIKE conditions in the same query.

SELECT * FROM customers 
WHERE 
  first_name ILIKE '%paul%' 
  AND last_name ILIKE '%smith%';

After executing the above query, I got the expected output as shown in the screenshot below.

Conclusion

In this PostgreSQL tutorial, you have learned how to use the ILIKE case-insensitive operator with single or multiple columns with examples. Also discussed using the underscore (_) and percent symbol (%) to match the pattern for single and various characters.

You may like to read:

• How to create user in PostgreSQL
• How to Set PostgreSQL User Password
• How To Take BackUp Of Postgres Database
• How To Concat In PostgreSQL
• PostgreSQL Add Primary Key To Existing Column