PostgreSQL ILIKE Keyword

The PostgreSQL ILIKE keyword is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the ILIKE keyword are given below:

Note: The NOT ILIKE keyword is the negation of ILIKE keyword.

Note: The ILIKE keyword is similar to LIKE keyword except the ILIKE keyword performs case-insensitive match according to the active locale and LIKE keyword performs case-sensitive match according to the active locale.

Wildcard Characters in PostgreSQL

Symbol	Description	Example
%	Represents zero, one or multiple characters.	'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_	Represents one character.	'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
ESCAPE	Allow test for literal instances of a wildcard character such as % or _	'J%!%' ESCAPE '!' represents a value that starts with J and ends in %, for example - John%, Jo% and Journey% etc.

Note: The operator ~~* is equivalent to ILIKE. Similarly, !~~* operator represents NOT ILIKE.

Syntax

The syntax for using ILIKE keyword in PostgreSQL is given below:

SELECT column1, column2, ...
FROM table_name
WHERE column ILIKE pattern;

The table below describes patterns which is used with ILIKE keyword and uses (%) and (_).

Pattern	Description
'J%'	A value that start with "J".
'%n'	A value that end with "n".
'%oh%'	A value that have "oh" in any position.
'_o%'	A value that have "o" in the second position.
'J_%'	A value that start with "J" and have at least 2 characters.
'J__%'	A value that start with "J" and have at least 3 characters.
'J%n'	A value that start with "J" and ends with "n".

Example:

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

To fetch all records from Employee table where the City starts with 'New', the query is:
```
SELECT * FROM Employee
WHERE City ILIKE 'New%';
```
This will produce the result as shown below:

EmpID Name City Age Salary

2 Marry New York 24 2750

5 Ramesh New Delhi 28 3000
Similarly, to fetch all records from Employee table where Name of the employee starts with 'Jo', the query is:
```
SELECT * FROM Employee
WHERE Name ILIKE 'Jo%';
```
This will produce the result as shown below:

EmpID Name City Age Salary

1 John London 25 3000

3 Jo Paris 27 2800
Using NOT ILIKE keyword: NOT ILIKE keyword is used as the negation of ILIKE keyword. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:
```
SELECT * FROM Employee
WHERE Name NOT ILIKE 'Jo%';
```
This will produce the result as shown below:

EmpID Name City Age Salary

2 Marry New York 24 2750

4 Kim Amsterdam 30 3100

5 Ramesh New Delhi 28 3000

6 Huang Beijing 28 2800

EmpID	Name	City	Age	Salary
2	Marry	New York	24	2750
5	Ramesh	New Delhi	28	3000

EmpID	Name	City	Age	Salary
2	Marry	New York	24	2750
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

❮ PostgreSQL Keywords