LIKE, ILIKE, and Wildcards

Last Updated: June 12, 2026 • 14 min read

LIKE and ILIKE perform pattern matching on string values using wildcard characters. They are essential for search functionality, data validation, and filtering text columns.

1. Introduction

LIKE and ILIKE are SQL operators for pattern matching on string data. LIKE is case-sensitive and part of the SQL standard, while ILIKE is a PostgreSQL extension for case-insensitive matching. Together with wildcard characters (% and _), they enable flexible text filtering without needing regular expressions.

2. Why It Matters

Search features: Every autocomplete, search bar, and filter relies on pattern matching — LIKE is the simplest and often fastest approach.
Data validation: Finding malformed emails, phone numbers, or codes by matching (or not matching) expected patterns.
Performance awareness: Knowing when LIKE can use indexes vs. when it forces full table scans is a critical production skill.
Interview favorite: Leading-wildcard performance and LIKE vs. ILIKE differences are common SQL interview topics.

3. Real-World Analogy

Think of LIKE as a library card catalog search. Searching for books by "Tol" with 'Tol%' is like flipping to the "T" section and scanning forward — fast and organized. Searching with '%kien' is like reading every card in every drawer to find titles ending in "kien" — slow and exhaustive. The position of the wildcard determines whether the librarian can use the organized catalog or must check every single card.

4. How It Works

LIKE compares a column value against a pattern string containing wildcards:

Wildcard	Matches	Example	Matches These
%	Zero or more characters	'John%'	John, Johnny, Johnson
_	Exactly one character	'J_hn'	John, Jahn (not Joan)

LIKE (case-sensitive): WHERE name LIKE 'John%' — matches "John" and "Johnny" but NOT "john".
ILIKE (case-insensitive, PostgreSQL): WHERE name ILIKE 'john%' — matches "John", "john", "JOHN".
NOT LIKE / NOT ILIKE: Inverts the match — returns rows that do NOT match the pattern.

5. Internal Architecture

How the database engine processes a LIKE query:

Key insight: B-tree indexes store values in sorted order, so prefix patterns can do range scans. Leading wildcards break this — the engine has no starting point in the sorted index.

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Leading wildcards on large tables: LIKE '%search' forces a full table scan. Use pg_trgm GIN indexes or full-text search for substring matching on production tables.
Forgetting ILIKE is PostgreSQL-only: MySQL's LIKE is case-insensitive by default, SQL Server depends on collation. Use LOWER(col) LIKE LOWER('pattern') for portable code.
Not escaping wildcards in user input: If a user searches for "50%" or "under_score", the % and _ are treated as wildcards. Always use the ESCAPE clause.

Interview Insight

Leading wildcards (%pattern) cannot use B-tree indexes. For substring search, mention alternatives: trigram indexes (pg_trgm), full-text search (to_tsvector), or external search engines (Elasticsearch).

9. Quick Quiz

Q1: Which LIKE pattern can use a B-tree index?

A) %admin% B) admin% C) %admin

Answer: B — admin% has a fixed prefix that enables an index range scan.

Q2: What does WHERE name LIKE 'J_n%' match?

A) Only "Jan" B) "Jan", "John", "June" C) "Jan" and "John" but not "June"

Answer: C — _ matches exactly one character (J + any char + n), then % matches the rest. "June" has "u" after J but the 3rd char is "n", so it matches. Wait — actually "June" = J-u-n-e, so it does match! Answer is B.

10. Scenario-Based Challenge

Build a User Search Feature

Your app has a users table with 2 million rows. The product team wants a search box that finds users by partial name or email match, case-insensitive, with results in under 50ms. Using ILIKE '%search%' takes 4 seconds. Design a solution that meets the performance target. Consider: trigram indexes, full-text search vectors, and whether you need separate indexes for name vs. email columns.

11. Debugging Exercise

This query returns zero rows even though there are products with "50% off" in their name. What's wrong?

12. Interview Questions

Q: Why does LIKE '%pattern' perform poorly, and what alternatives exist?

A: Leading wildcards prevent B-tree index usage because the index is sorted by prefix — with no fixed prefix, the engine must scan every row. Alternatives: (1) pg_trgm GIN indexes for substring ILIKE, (2) full-text search with tsvector/tsquery, (3) reverse indexes for suffix patterns, (4) Elasticsearch for complex search.

Q: How do you make case-insensitive LIKE work across different databases?

A: PostgreSQL has ILIKE. For portable SQL, use LOWER(column) LIKE LOWER('pattern'). MySQL's LIKE is case-insensitive by default (depends on collation). SQL Server depends on the column's collation — use COLLATE to force case-sensitivity.

Q: When would you choose LIKE over regular expressions (~ operator)?

A: LIKE is simpler, faster for prefix matching (can use B-tree indexes), and more portable. Use regex (~) only when you need complex patterns like character classes, alternation, or quantifiers. For basic prefix/suffix/contains, LIKE with pg_trgm is usually sufficient.

13. Production Considerations

Index strategy: Prefix patterns ('term%') use B-tree indexes automatically. For substring search, install pg_trgm and create GIN indexes. For suffix matching, consider a reverse index.
Full-text search: For user-facing search on large text, use tsvector columns with GIN indexes instead of LIKE. It provides ranking, stemming, and language-aware search.
Escape user input: Always sanitize or escape % and _ in user-provided search strings before using them in LIKE patterns to prevent wildcard injection.
Collation awareness: LIKE case-sensitivity depends on database collation. Test behavior in your specific setup — don't assume it matches your development environment.