Pattern matching with LIKE and ILIKE in PostgreSQL - Deep Dive

Overview - Pattern matching with LIKE and ILIKE

What is it?

Pattern matching with LIKE and ILIKE lets you search text in a database by using simple wildcards. LIKE matches text with case sensitivity, meaning uppercase and lowercase letters must match exactly. ILIKE works like LIKE but ignores case differences, so 'Apple' and 'apple' are treated the same. These tools help find rows where text fits a pattern, like names starting with 'A' or emails ending with '.com'.

Why it matters

Without pattern matching, searching text in databases would be rigid and exact, missing useful results. For example, finding all customers whose names start with 'Jo' would require knowing the full name. LIKE and ILIKE let you find flexible matches, saving time and making data searches more human-friendly. This is crucial for apps like search engines, filters, and reports that rely on text queries.

Where it fits

Before learning pattern matching, you should understand basic SQL SELECT queries and how to filter data with WHERE clauses. After mastering LIKE and ILIKE, you can explore more advanced text search tools like regular expressions and full-text search in PostgreSQL.

Mental Model

Core Idea

LIKE and ILIKE let you find text that fits a simple pattern using wildcards, with ILIKE ignoring letter case.

Think of it like...

It's like using a magnifying glass to find words in a book where some letters can be any letter, and with ILIKE, you don't care if the letters are uppercase or lowercase.

Pattern Matching Flow:

Input Text  ──> [LIKE or ILIKE] ──> Match Pattern with Wildcards

Wildcards:
  %  = any sequence of characters (including none)
  _  = any single character

LIKE: Case-sensitive match
ILIKE: Case-insensitive match

Build-Up - 7 Steps

1

Foundation

Basic LIKE pattern matching

Concept: Introduction to the LIKE operator and its wildcards.

The LIKE operator lets you search for text that fits a pattern. Use % to match any number of characters and _ to match exactly one character. For example, WHERE name LIKE 'Jo%' finds names starting with 'Jo'.

Result

You get all rows where the text matches the pattern exactly with case sensitivity.

Understanding LIKE's wildcards is the first step to flexible text searching in databases.

2

Foundation

Case sensitivity in LIKE

Concept: LIKE matches text exactly, including uppercase and lowercase letters.

If you search WHERE name LIKE 'jo%', it won't find 'John' because 'J' is uppercase. LIKE treats 'J' and 'j' as different characters.

Result

Queries with LIKE can miss matches if letter case differs.

Knowing LIKE is case-sensitive helps avoid unexpected empty results.

3

Intermediate

Using ILIKE for case-insensitive search

🤔Before reading on: Do you think ILIKE matches text exactly like LIKE or ignores case differences? Commit to your answer.

Concept: ILIKE works like LIKE but ignores case differences in matching.

Using WHERE name ILIKE 'jo%' finds 'John', 'john', or 'JOHN'. It treats uppercase and lowercase letters as the same.

Result

You get all rows matching the pattern regardless of letter case.

ILIKE makes text searches more user-friendly by ignoring case, which is common in real-world data.

4

Intermediate

Combining wildcards for complex patterns

🤔Before reading on: Can you use multiple wildcards together in LIKE patterns? Predict what WHERE email LIKE '%@%.com' matches.

Concept: You can combine % and _ wildcards to build detailed search patterns.

For example, WHERE email LIKE '%@%.com' finds emails ending with '.com' and containing '@' somewhere before. WHERE code LIKE 'A__%' finds codes starting with 'A' followed by any two characters, then anything else.

Result

You can filter text with precise patterns using multiple wildcards.

Mastering wildcard combinations unlocks powerful text filtering capabilities.

5

Intermediate

Escaping wildcards in patterns

Concept: Sometimes you want to search for the actual % or _ characters, not as wildcards.

Use the ESCAPE keyword to define an escape character. For example, WHERE name LIKE '100\%' ESCAPE '\' finds names containing '100%'. Without escaping, % would match any characters.

Result

You can search for literal wildcard characters in text.

Knowing how to escape wildcards prevents accidental broad matches and errors.

6

Advanced

Performance considerations with LIKE and ILIKE

🤔Before reading on: Do you think queries with leading % in LIKE patterns use indexes efficiently? Yes or no?

Concept: Patterns starting with % prevent use of indexes, slowing queries.

If you write WHERE name LIKE '%son', the database must scan all rows because it can't use indexes efficiently. Patterns starting without % like 'Jo%' can use indexes and run faster.

Result

Query speed depends on pattern structure; leading wildcards hurt performance.

Understanding how pattern structure affects indexing helps write faster queries.

7

Expert

Differences between LIKE/ILIKE and regex matching

🤔Before reading on: Is LIKE more powerful than regular expressions for pattern matching? Commit to yes or no.

Concept: LIKE and ILIKE use simple wildcards, while regex offers complex pattern matching.

LIKE uses % and _ wildcards only. Regex can match complex patterns like optional characters, repetitions, or character classes. Regex is more powerful but also more complex and slower.

Result

You know when to use simple LIKE/ILIKE versus advanced regex for text search.

Recognizing the limits of LIKE/ILIKE guides choosing the right tool for text matching.

Under the Hood

PostgreSQL processes LIKE and ILIKE by scanning text values and comparing them to the pattern using simple wildcard rules. LIKE compares characters byte-by-byte considering case, while ILIKE converts both sides to lowercase internally before comparing. The database can use indexes for patterns starting with fixed text but must scan rows when patterns start with wildcards. Internally, ILIKE adds overhead due to case normalization.

Why designed this way?

LIKE was designed as a simple, easy-to-use pattern matching tool for SQL, balancing power and simplicity. ILIKE was added later to handle case-insensitive needs common in user-facing applications. The design avoids full regex complexity for performance and usability. Index usage rules stem from how B-tree indexes work, which require fixed prefixes for efficient lookups.

┌───────────────┐
│   Input Text  │
└──────┬────────┘
       │
       ▼
┌───────────────┐       ┌───────────────┐
│   Pattern     │──────▶│ LIKE or ILIKE │
│ (with % and _)│       └──────┬────────┘
└───────────────┘              │
                               ▼
                    ┌─────────────────────┐
                    │ Case-sensitive (LIKE)│
                    │ or Case-insensitive  │
                    │     (ILIKE)          │
                    └─────────┬───────────┘
                              │
                ┌─────────────┴─────────────┐
                │  Use index if pattern starts│
                │  with fixed text (no leading│
                │  %) else full scan          │
                └─────────────┬─────────────┘
                              │
                              ▼
                      ┌─────────────┐
                      │  Result Set │
                      └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LIKE ignore case differences by default? Commit to yes or no.

Common Belief: LIKE ignores case differences and matches text regardless of uppercase or lowercase letters.

Reality: LIKE is case-sensitive and treats uppercase and lowercase letters as different.

Why it matters: Assuming LIKE ignores case can cause queries to miss matching rows, leading to incomplete or wrong results.

Quick: Can you use regular expressions inside LIKE patterns? Commit to yes or no.

Common Belief: LIKE supports full regular expressions for complex pattern matching.

Reality: LIKE only supports simple wildcards % and _, not full regex patterns.

Why it matters: Expecting regex in LIKE leads to incorrect queries and confusion; use PostgreSQL's ~ operator for regex.

Quick: Does a pattern starting with % use indexes efficiently? Commit to yes or no.

Common Belief: Patterns starting with % can use indexes just like those starting with fixed text.

Reality: Leading % prevents index use, causing slower full table scans.

Why it matters: Ignoring this can cause performance problems in large databases.

Quick: Does ILIKE always perform faster than LIKE? Commit to yes or no.

Common Belief: ILIKE is faster because it ignores case and matches more easily.

Reality: ILIKE is usually slower because it must convert text to lowercase before comparing.

Why it matters: Assuming ILIKE is faster can lead to inefficient queries in performance-critical applications.

Expert Zone

1

ILIKE internally converts both the pattern and the text to lowercase using the database collation, which can affect performance and sorting behavior subtly.

2

PostgreSQL supports index types like pg_trgm that can accelerate LIKE and ILIKE queries even with leading wildcards, but require special setup.

3

The ESCAPE clause in LIKE patterns allows searching for literal wildcard characters, but forgetting to use it can cause unexpected matches or errors.

When NOT to use

Avoid LIKE and ILIKE when you need complex pattern matching like repetitions, optional parts, or character classes; use PostgreSQL's regular expression operators (~, ~*). Also, for very large text searches, consider full-text search features instead of LIKE/ILIKE for better performance and relevance.

Production Patterns

In real systems, LIKE is often used for simple prefix searches (e.g., WHERE name LIKE 'Jo%') to leverage indexes. ILIKE is common in user-facing search features to ignore case. For more complex needs, developers combine pg_trgm indexes with ILIKE or use full-text search. Escaping wildcards is important in user input to avoid injection or wrong matches.

Connections

Regular Expressions

LIKE/ILIKE provide simple pattern matching, while regular expressions offer advanced, complex matching.

Understanding LIKE's limits helps appreciate when to use regex for powerful text searches.

Indexing in Databases

LIKE patterns starting with fixed text can use indexes; patterns with leading wildcards cannot.

Knowing how pattern structure affects index use is key to writing efficient queries.

Human Language Processing

ILIKE's case-insensitive matching mimics how humans read text ignoring letter case differences.

Recognizing this connection helps design user-friendly search features that match human expectations.

Common Pitfalls

#1 Using LIKE with a pattern that starts with % expecting fast query performance.

Wrong approach:

SELECT * FROM users WHERE name LIKE '%son';

Correct approach:

SELECT * FROM users WHERE name LIKE 'Son%';

Root cause:

Misunderstanding that leading % disables index use, causing slow full scans.

#2 Using LIKE when case-insensitive search is needed, causing missed matches.

Wrong approach:

SELECT * FROM products WHERE category LIKE 'electronics%';

Correct approach:

SELECT * FROM products WHERE category ILIKE 'electronics%';

Root cause:

Not realizing LIKE is case-sensitive and misses matches with different letter cases.

#3 Searching for literal % character without escaping, causing wrong matches.

Wrong approach:

SELECT * FROM discounts WHERE code LIKE 'SAVE%';

Correct approach:

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

Root cause:

Forgetting to escape % when it should be treated as a normal character.

Key Takeaways

LIKE and ILIKE let you search text with simple patterns using % and _ wildcards.

LIKE is case-sensitive; ILIKE ignores case differences, making searches more flexible.

Patterns starting with fixed text can use indexes for fast queries; leading % disables this.

Escaping wildcards is necessary to search for literal % or _ characters.

For complex patterns or large text searches, consider regular expressions or full-text search instead.