SQL ILIKE Function
Quick summary: The SQL ILIKE operator performs a case-insensitive pattern match (PostgreSQL only).
SQL ILIKE Syntax
WHERE column_name ILIKE patternSQL
SQL ILIKE Basic examples
SELECT * FROM users WHERE email ILIKE '%gmail.com';SQL
Output:
All Gmail users (case-insensitive)Matches values ignoring case.
SQL ILIKE Real-world usage
SELECT * FROM products WHERE name ILIKE '%phone%';SQL
Output:
Products containing "phone"Implements case-insensitive search.
SQL ILIKE Edge cases
SELECT * FROM users WHERE name ILIKE 'JOHN';SQL
Output:
john, John, JOHNMatches regardless of letter case.
SQL ILIKE Common mistakes
Using ILIKE in MySQL
ILIKE is not supported outside PostgreSQL.
Incorrect
SELECT * FROM users WHERE name ILIKE 'john';Correct
SELECT * FROM users WHERE LOWER(name) LIKE 'john';Use LOWER() + LIKE in MySQL.
SQL ILIKE Frequently Asked Questions
What does ILIKE do in SQL?
Performs case-insensitive pattern matching.
Which databases support ILIKE?
PostgreSQL.
Use case of ILIKE?
Case-insensitive search.
Difference from LIKE?
ILIKE is case-insensitive.
Supports wildcards?
Yes (% and _).
Common mistake?
Using in unsupported DBs.
Performance impact?
Can be slower without indexes.
Alternative?
LOWER(column) LIKE.
Used with WHERE?
Yes.
Supports indexes?
Limited.
Best practice?
Use functional indexes.
Used in search?
Yes.