SQL Reference

ILIKE

ILIKE is a case-insensitive search operation in SQL. This command is utilized to match a specified pattern within columns. Unlike LIKE, ILIKE doesn't differentiate between lower case and upper case letters. It's important to note that ILIKE is specific to PostgreSQL and may not be adaptable to other SQL platforms.

Example

SELECT name
FROM students
WHERE name ILIKE '%John%';

Output

    name
--------------
  John Doe
  John Smith

Explanation

The ILIKE operator in the SQL query matches the student names in the PostgreSQL database which are containing ‘John’ regardless of the case.

Example

SELECT * FROM Employees
WHERE Employee_Name ILIKE '%doe%';

Output

| Employee_ID | Employee_Name  | Occupation  | Salary |
| ----------- | -------------- | ----------- | ------ |
| 3           | John Doe       | Accountant  | 50000  |
| 5           | Jane Doe       | Designer    | 60000  |

Explanation

In the code, ILIKE keyword is used in the WHERE clause to find all records where Employee_Name includes the substring ‘doe’, without pondering about case sensitivity. The output shows returned employees whose names match the criteria.