SQL ILIKE

What does SQL ILIKE do?

ILIKE performs case-insensitive pattern matching, returning true when the left operand matches the supplied pattern.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ILIKE: PostgreSQL, Amazon Redshift, CockroachDB, YugabyteDB. Not supported in MySQL, SQL Server, Oracle, or SQLite.

SQL ILIKE Full Explanation

ILIKE is a PostgreSQL-specific operator that functions like LIKE but ignores letter case by internally applying a case-insensitive collation. It evaluates to a Boolean and is most often used in WHERE clauses to filter rows whose text column matches a supplied pattern containing wildcard characters. Supported wildcards are % (any sequence of characters, including zero) and _ (any single character). You can optionally specify a custom escape character with the ESCAPE clause to treat % or _ as literals. Because ILIKE bypasses indexes that use the default case-sensitive collation, performance may degrade on large tables unless you add a functional index on lower(column) or use a case-insensitive collation index in PostgreSQL 12+. Unlike LIKE, ILIKE is not in the SQL standard and is unavailable in most other database systems.

SQL ILIKE Syntax

expression ILIKE pattern [ ESCAPE escape_character ]

SQL ILIKE Parameters

expression (string) - The text value to compare.
pattern (string) - The pattern including % and _ wildcards.
escape_character (optional char) - Single character that escapes % or _ inside the pattern.

Example Queries Using SQL ILIKE

-- Find users with an email ending in "/example.com" (any case)
SELECT *
FROM users
WHERE email ILIKE '%@example.com';

-- Check if a variable contains "galaxy" regardless of case
SELECT 'GaLaXy 2024' ILIKE '%galaxy%';

-- Use ESCAPE to search for the actual underscore character
SELECT 'a_b' ILIKE 'a\_b' ESCAPE '\';

Expected Output Using SQL ILIKE

The first query returns all rows whose email ends with @example
com in any letter case
The second query returns a single Boolean value true
The third query returns true, because the pattern treats the underscore as a literal due to the ESCAPE clause

Use Cases with SQL ILIKE

Filtering case-insensitive user input such as emails or usernames
Implementing search boxes where capitalization should not matter
Matching domain names, tags, or codes with varying case
Creating validation checks inside PL/pgSQL functions

Common Mistakes with SQL ILIKE

Assuming ILIKE exists in MySQL or SQL Server (it does not)
Forgetting that ILIKE can skip B-tree indexes, causing slow queries
Misusing ESCAPE by providing more than one character
Expecting pattern matching to respect locale-specific case rules on very old PostgreSQL versions

First Introduced In

PostgreSQL 6.4

Frequently Asked Questions

What is the difference between LIKE and ILIKE?

LIKE is case-sensitive under a case-sensitive collation, whereas ILIKE ignores case entirely.

Does ILIKE use indexes?

Not by default. Create a functional index on lower(column) or a case-insensitive collation index to improve speed.

How do I search for a literal percent sign with ILIKE?

Add an ESCAPE clause and prefix the percent sign with the escape character, for example: `WHERE col ILIKE '%25' ESCAPE '\';`.

Can ILIKE be used with non-text data types?

PostgreSQL automatically casts varchar, char, and text. Cast other types (e.g., `::text`) before using ILIKE.