LIKE and ILIKE Pattern Matching

You've built tables, written joins, and filtered rows with =. But what happens when you need to find customers whose names you only half-remember, or a task asks you to pull every product with "Pro" in the title? The equality operator fails you immediately. This is the exact moment LIKE and ILIKE enter the picture, giving you the wildcard power to search for patterns instead of exact values — a skill that separates basic SQL from genuinely useful SQL.

The problem this lesson solves

Exact-match filtering has a hard limit. WHERE name = 'alice' returns precisely that string and nothing else. If you want to find users whose email domains end in @gmail.com, or projects whose names contain the word "api", you're stuck. You can't enumerate every possible value, and case-sensitivity makes the problem worse — is it Alice, alice, or ALICE?

The pain is real: you write a query, get zero rows, and silently suspect your data is broken when in fact your pattern was too narrow. Without pattern matching, you end up pulling whole tables into memory and filtering in application code — a performance crime at any scale.

LIKE and ILIKE are PostgreSQL's built-in answer to this. They let you define a pattern with wildcards, and the database engine does the heavy lifting. This lesson walks you through the syntax, the mental model, the gotchas, and the decision points so you can search text with confidence.

Core concept / mental model

Think of LIKE as a sophisticated = operator for strings that understands wildcards. Instead of asking "is this column exactly this value?", it asks "does this column fit this pattern?"

There are two wildcards you must know:

• % (percent) — matches zero or more characters. It's the "anything" wildcard.
• _ (underscore) — matches exactly one character. It's the "any single character" wildcard.

The difference between the two operators:

ILIKE is technically implemented as LOWER(column) LIKE LOWER(pattern), but you don't need to worry about that. Just remember: use LIKE when case matters, ILIKE when it doesn't.

A mental picture: imagine the pattern as a string of beads. % is a stretchy bead that absorbs any number of characters; _ is a fixed-size bead that takes exactly one. When the pattern runs alongside your text, every bead must have a place to sit for a match.

How it works step by step

Using LIKE is a three-part process: define your pattern, attach it to a column, and let PostgreSQL evaluate it.

1. Identify the column you want to search — e.g., product_name.
2. Decide on case sensitivity — use LIKE for case-sensitive, ILIKE for case-insensitive.
3. Craft your pattern with % and _ wildcards, placing them strategically.

Let's break down the pattern logic with examples. Suppose a column contains 'PostgreSQL Database'.

• 'PostgreSQL%' — matches. The % swallows ' Database'.
• '%Database' — matches. The % swallows 'PostgreSQL '.
• '%SQL%' — matches. The % swallows both ends.
• 'PostgreSQL _atabase' — matches. The _ takes the single D.
• 'PostgreSQL Database' — matches. No wildcards needed, just an exact match.
• '%database' — fails with LIKE because of the capital D; succeeds with ILIKE.

The key mental shift: % is zero or more, so 'a%' matches 'a' itself, as well as 'ab' and 'a' followed by anything. _ is exactly one, so 'a_' matches 'ab' but not 'a' alone.

Play with these combinations in your head before running them in psql. The % wildcard is far more common than _ in everyday queries.

Hands-on walkthrough

Let's get your hands dirty. Fire up psql and create a small test table to play with.

First, set up the sample data:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT
);

INSERT INTO products (name, category) VALUES
    ('Laptop Pro', 'electronics'),
    ('laptop Air', 'electronics'),
    ('Coffee Mug', 'kitchen'),
    ('COFFEE GRINDER', 'kitchen'),
    ('Desk Lamp', 'office');

Now, run your first LIKE query:

SELECT name FROM products WHERE name LIKE 'Laptop%';

Expected output:

    name
-----------
 Laptop Pro
(1 row)

Notice only the exact-capitalized Laptop Pro appears. Now try ILIKE to catch the lowercase variant:

SELECT name FROM products WHERE name ILIKE 'laptop%';

Expected output:

    name
-----------
 Laptop Pro
 laptop Air
(2 rows)

Both rows match because case is ignored. Let's get more creative — find all kitchen items, regardless of case:

SELECT name FROM products WHERE category = 'kitchen' AND name ILIKE '%coffee%';

Expected output:

      name
-----------------
 Coffee Mug
 COFFEE GRINDER
(2 rows)

The %coffee% pattern matches strings that contain "coffee" anywhere.

Finally, let's use the underscore wildcard to find names with a specific single-character gap:

SELECT name FROM products WHERE name LIKE 'Des_ Lamp';

Expected output:

   name
----------
 Desk Lamp
(1 row)

The _ matched the k in Desk. If you're ever unsure about what a pattern will match, build a tiny test case like this and verify. The REPL makes experimenting cheap.

Pro tip: Always prefix your pattern-matching queries with a quick EXPLAIN if they run against large tables. An unindexed LIKE '%foo' pattern forces a full table scan — your DBA will thank you for respecting the leading wildcard.

Compare options / when to choose what

LIKE isn't the only pattern-matching tool in PostgreSQL. When should you reach for the alternatives? Here's a comparison table:

General guidance:

• Use LIKE/ILIKE for 90% of your pattern matching. They're clear, standard, and cover most business logic.
• Reach for ~ regex when you need alternation (a|b), character classes ([0-9]), or repetition quantifiers ({2,3}).
• Skip SIMILAR TO entirely unless you're migrating from other SQL databases that use it — it's a weird middle ground that lacks the performance benefits of regex and the simplicity of LIKE.
• If a %word% search is a hot path in your application, seriously investigate pg_trgm GIN indexes. It's a production-grade solution for what would otherwise be a full scan every time.

Troubleshooting & edge cases

Pattern matching is deceptively simple, and several traps wait for the unwary. Here are the most common issues and how to fix them.

Case-sensitivity surprises

You search WHERE name LIKE 'alice%' and get nothing, even though Alice is clearly in the table. This is the #1 gotcha. LIKE is case-sensitive; ILIKE is not. If you want case-insensitivity, either use ILIKE explicitly, or make your pattern case-insensitive:

WHERE name ILIKE 'alice%'  -- preferred
WHERE LOWER(name) LIKE 'alice%'  -- verbose, identical effect

The ESCAPE clause behavior

What if your data literally contains % or _? For example, a column storing discount codes like SAVE_20%. A naive query WHERE code LIKE 'SAVE%' matches everything starting with SAVE, including SAVE_20%. To match the literal characters, escape them:

SELECT * FROM coupons WHERE code LIKE 'SAVE\_20\%' ESCAPE '\';

The ESCAPE '\' clause tells PostgreSQL to treat \_ and \% as the literal underscore and percent characters.

NULLs never match

NULL LIKE '%anything%' evaluates to NULL, which in a WHERE clause is treated as false. Rows with NULL in the matched column will never appear, even with a pattern like % that matches everything. To include NULLs, add an OR col IS NULL condition.

Leading wildcard and performance

A pattern starting with % (e.g., '%search_term') prevents PostgreSQL from using a regular B-tree index on that column. The database must scan every row. If speed matters, don't start patterns with % (use a prefix pattern like 'search_term%'), or use the pg_trgm extension for substring search indexing.

What you learned & what's next

You've conquered the art of LIKE and ILIKE pattern matching. You understand the wildcard semantics of % (zero or more) and _ (exactly one), you know which operator to use based on case sensitivity, and you can troubleshoot the classic gotchas around escaping, NULLs, and index performance.

You learned:

• LIKE is case-sensitive; ILIKE is case-insensitive.
• % matches zero or more characters; _ matches exactly one.
• Escape % and _ with ESCAPE when searching for literal wildcards.
• Leading wildcards hurt index usage; plan for it with pg_trgm if needed.
• The ILIKE implementation is effectively a LOWER() wrapper — keep that in mind for edge-case comparisons.

Now that you can find any needle in any haystack, the next step in this PostgreSQL track is aggregating and grouping data. You'll learn to count, sum, and categorize your matched rows into meaningful summaries — turning raw search results into actionable insight.