ILIKE Case Insensitive in PostgreSQL

In this PostgreSQL tutorial, I will show how to use ILIKE Case Insensitive 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.

ILIKE Case Insensitive in PostgreSQL using SELECT

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 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 take an example using the below command.

SELECT 'United States' ILIKE 'united states';
ILIKE Case Insensitive in PostgreSQL

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 or some string and you want to make a pattern matching with 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 based on the underscore and percent sign using the below commands.

SELECT 'United States' ILIKE '_nited states';
ILIKE Case Insensitive in PostgreSQL Underscore

If you look at the above query in the pattern ‘_nited states’, the underscore(_) symbol is used, and this tells 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 with 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 or matches the whole string.

SELECT 'United States' ILIKE '%states';
ILIKE Case Insensitive in PostgreSQL Percent Sign

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.

ILIKE Case Insensitive in PostgreSQL on 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 name of all the employees whose name contains the letter ‘e’.

SELECT name From employees
WHERE name ILIKE '%e%';
ILIKE Case Insensitive in PostgreSQL on Single Column

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

ILIKE Case Insensitive in PostgreSQL on Multiple Column

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 that contains the letter ‘e’ with a salary greater than $52000.


SELECT name,salary From employees
WHERE name ILIKE '%e%' and salary > 52000;
ILIKE Case Insensitive in PostgreSQL on Multiple Column

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

Conclusion

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

You may like to read: