PostgreSQL ILIKE: Case-Insensitive Search for Beginners

**PostgreSQL ILIKE** is how you search text without caring about uppercase or lowercase letters. If you type `WHERE name ILIKE 'anna%'`, PostgreSQL finds Anna, anna and ANNA. In this guide you will learn what `LIKE` and `ILIKE` do, what the `%` and `_` wildcards mean, and how to build a simple search box query. No experience needed.

A quick definition first. A **database** stores information in **tables**. A table has **rows** (one item each) and **columns** (one kind of detail). A **query** is a question you ask the database. **PostgreSQL** (often called Postgres) is a popular free database.

What are LIKE and ILIKE?

`LIKE` checks whether text matches a **pattern**. A pattern is text with special symbols that stand for 'any characters'. `ILIKE` does the same thing but ignores case. The 'I' stands for 'insensitive', which means 'does not care about upper or lower case'.

`ILIKE` is special to PostgreSQL. Many other databases do not have it. If you already read our SQL LIKE guide, this is the Postgres upgrade of that idea.

Set up a small practice table

Run this in psql or any PostgreSQL tool. It makes a table of books and adds five rows.

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL
);

INSERT INTO books (title, author) VALUES
  ('Learn SQL Fast', 'Anna Lee'),
  ('The Postgres Handbook', 'anna smith'),
  ('Cooking for Beginners', 'Omar Khan'),
  ('SQL Tips', 'ANNA BROWN'),
  ('Gardening Basics', 'Luis Perez');

Line by line: `CREATE TABLE books` makes a table named books. `id SERIAL PRIMARY KEY` gives every row its own number that counts up by itself. `title TEXT NOT NULL` and `author TEXT NOT NULL` are text columns that cannot be empty. `INSERT INTO` adds rows, and each pair in brackets is one book. Notice the three authors named Anna are written in different cases. That is on purpose.

The wildcards: % and _

A **wildcard** is a symbol that stands in for other characters. PostgreSQL has two.

Step 1: Search with LIKE

SELECT title, author
FROM books
WHERE author LIKE 'Anna%';

`SELECT title, author` picks the two columns to show. `FROM books` says which table. `WHERE` filters the rows. `LIKE 'Anna%'` means 'starts with Anna, then anything'. Only 'Anna Lee' is returned. 'anna smith' and 'ANNA BROWN' are missed, because `LIKE` cares about case.

Step 2: Search with ILIKE

SELECT title, author
FROM books
WHERE author ILIKE 'anna%';

The only change is `ILIKE`. Now all three Annas come back, because case no longer matters. This is the best choice for search boxes, since people type in any case.

Step 3: Find text anywhere inside

Put a `%` on both sides to find a word anywhere in the text.

SELECT title
FROM books
WHERE title ILIKE '%sql%';

`'%sql%'` means 'anything, then sql, then anything'. It returns 'Learn SQL Fast' and 'SQL Tips'. Note that a pattern starting with `%` cannot use a normal index, so on very large tables it can be slow. For a learning project this is fine.

Step 4: Match ends and single characters

-- ends with 'Basics'
SELECT title FROM books WHERE title ILIKE '%basics';

-- author name has exactly 4 letters after 'Omar '
SELECT author FROM books WHERE author ILIKE 'omar _han';

The first query uses `%` only at the start, so it finds titles that end in 'basics'. The second uses `_` to stand for exactly one character, so 'Omar Khan' matches 'omar _han'. Two dashes start a **comment**, a note that PostgreSQL ignores.

Step 5: NOT ILIKE and combining conditions

SELECT title, author
FROM books
WHERE title ILIKE '%sql%'
  AND author NOT ILIKE '%brown%';

`NOT ILIKE` returns rows that do NOT match. `AND` means both conditions must be true. The result is 'Learn SQL Fast', because 'SQL Tips' is written by Brown and is removed.

Search for a real % or _ character

What if you want to find a literal percent sign, such as '50%'? Put a backslash before it. This is called **escaping**.

SELECT * FROM books WHERE title ILIKE '%50\%%';

Here `\%` means a real percent sign, and the other two `%` are wildcards. PostgreSQL uses the backslash as the default escape character.

LIKE vs ILIKE at a glance

Common beginner mistakes

• Forgetting the wildcard: `ILIKE 'anna'` only matches the exact word anna, with nothing else.
• Using double quotes. Text values need single quotes: 'anna%'.
• Using `=` when you want a pattern. `=` needs an exact match.
• Adding a leading `%` on a huge table and wondering why it is slow.