ILIKE (Case-Insensitive LIKE)
beginnerfiltering
2 min read
What is ILIKE?
ILIKE is a PostgreSQL-specific operator for case-insensitive pattern matching. It works exactly like LIKE but ignores letter casing, so 'Alice', 'ALICE', and 'alice' are all matches for the pattern 'alice'.
Syntax
SELECT * FROM table_name
WHERE column ILIKE pattern;Wildcards:
%— matches any sequence of characters_— matches any single character
When to Use
- Searching for names or text regardless of how they were entered
- Implementing search features where users may type in any case
- Matching email addresses (which are case-insensitive by convention)
Key Points
- PostgreSQL Only — ILIKE is not part of standard SQL. In other databases, use LOWER(col) LIKE LOWER(pattern).
- Wildcards —
%and_work the same as in LIKE. - Performance — ILIKE cannot use a regular B-tree index. Consider a trigram (pg_trgm) index for better performance.
- NOT ILIKE — Negate with NOT ILIKE to exclude matches.
- Alternative — You can use
column ~* 'regex'for case-insensitive regex matching.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
ILIKE (Case-Insensitive LIKE) Challenge
Write a query that find students whose first name contains 'ali' regardless of case.
Expected result
Returns rows like Alice, ALICIA, ali, etc.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is ILIKE?
Initializing database...Each run starts from fresh sample data.
More Examples
ILIKE with underscore wildcard
Find 4-letter first names starting with J.
Initializing database...Each run starts from fresh sample data.
NOT ILIKE
Find products whose name does not contain 'test'.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is ILIKE standard SQL?
No. ILIKE is a PostgreSQL extension. For portability, use LOWER(column) LIKE LOWER(pattern).
How can I make ILIKE faster?
Create a trigram index: CREATE INDEX idx ON table USING gin (column gin_trgm_ops); after enabling the pg_trgm extension.