Exact equality and IN lists work when you know the complete value you're searching for. But what about finding all tracks with "night" somewhere in the title, or all users with Gmail addresses?

Pattern matching lets you filter strings by shape rather than exact content, and SQL gives you several tools for this, from simple wildcards to full regular expressions.

Insert the following rows to follow along:

LIKE compares a string value against a pattern. The pattern can contain two special wildcard characters:

The basic syntax:

Prefix Matching (Starts With)

Find all tracks whose title starts with "Sun":

The % after "Sun" matches any remaining characters, including zero characters.

Suffix Matching (Ends With)

Find all tracks whose title ends with "e":

The % before "e" matches everything up to the final character.

Substring Matching (Contains)

Find all tracks with "night" anywhere in the title:

Wrapping the search term in % on both sides matches it regardless of position. But notice that this only matches "Midnight Sun" and not "Late Night Drive" because LIKE is case-sensitive in PostgreSQL.

Single-Character Wildcard

The underscore matches exactly one character.

Example: Find all usernames that are exactly 7 characters long

The trick with _ is counting precisely.

A better use case: find usernames where the second character is "a":

The _ matches one character (any), then "a" matches literally, then % matches the rest.

Combining Wildcards

You can mix % and _ in a single pattern. Find artists whose name has exactly four characters before a space:

Four underscores followed by % matches any name with four or more characters.

Here is a more practical example, finding tracks with exactly two words (one space):

The first condition requires at least one space. The second excludes titles with two or more spaces.

In standard SQL and PostgreSQL, LIKE is case-sensitive. 'Night' does not match 'night':

This returns "Late Night Drive" but not "Midnight Sun" (lowercase "night" does not appear in "Midnight").

PostgreSQL provides ILIKE for case-insensitive pattern matching:

This matches both "Late Night Drive" (capital N) and "Midnight Sun" (lowercase n-i-g-h-t embedded in "Midnight").

Dialect Differences

Case sensitivity behavior varies across databases:

This is a common source of bugs when migrating between databases. A query that works correctly in MySQL (matching "night" regardless of case) will silently miss rows in PostgreSQL unless you switch to ILIKE.

To get case-insensitive matching without ILIKE, you can also lower-case both sides:

This works in all databases but prevents index usage on the title column. PostgreSQL's ILIKE has the same index limitation unless you create an expression index on LOWER(title).

The % and _ characters are wildcards inside LIKE patterns. But what if the actual data contains a literal percent sign or underscore?

Our sample data has a username ian_50%off with a literal %, and test_user_1 with underscores that are part of the actual name. If you search naively:

This matches any username containing "50" followed by anything, because both % characters around "50" are wildcards and the third % is also a wildcard. It does not restrict the match to a literal percent sign.

The ESCAPE clause designates a character that removes the special meaning of the next wildcard:

Here, \% means a literal percent sign. The pattern reads: anything, then "50", then a literal %, then anything. This correctly matches only ian_50%off.

You can use any character as the escape character, though backslash is the convention:

Escaping Underscores

Similarly, to find usernames containing a literal underscore:

Without the ESCAPE clause, _ matches any single character, so '%_%' matches every username with at least one character (which is all of them). With the escape, \_ matches only a literal underscore.

Another example: find usernames where an underscore is followed by a digit:

PostgreSQL Default Behavior

PostgreSQL treats backslash as the default escape character in LIKE patterns even without an explicit ESCAPE clause. This means '\%' already means a literal percent in PostgreSQL.

However, this behavior is non-standard and can be disabled with SET standard_conforming_strings = on (which is the default since PostgreSQL 9.1). In practice, always use an explicit ESCAPE clause for clarity and portability.

NOT LIKE returns rows where the pattern does not match:

This returns all artists whose name does not start with "The ".

NULL Behavior

LIKE follows the same NULL rules as other comparisons. If the column is NULL, the result is UNKNOWN (not TRUE, not FALSE):

For artists where country is NULL (Chromatic, Silent Echo, Phantom Wave), the LIKE expression evaluates to NULL, not FALSE. This means:

If you need to include NULLs, handle them explicitly:

PostgreSQL extends pattern matching beyond LIKE with the SIMILAR TO operator. It combines LIKE's % and _ wildcards with regex-style features:

The key difference from LIKE: SIMILAR TO must match the entire string (it's implicitly anchored), and % and _ still work as wildcards.

Find artists whose name starts with "The" OR ends with "Waves":

This matches "The Midnight Signal", "The Spark", and "The Rolling Waves".

Find artists whose name starts with "The" OR ends with "Waves" OR contains "Spark":

This returns "The Midnight Signal", "The Spark", "The Rolling Waves". "The Spark" matches both the first and third alternatives.

Character Classes in SIMILAR TO

This matches usernames containing at least one digit: test_user_1 and ian_50%off.

SIMILAR TO Limitations

SIMILAR TO is PostgreSQL-specific and somewhat awkward. It lives in a middle ground between LIKE (too simple) and full regex (more powerful). Many developers skip it and go straight to regex operators when LIKE is insufficient.

PostgreSQL supports full POSIX regular expressions with the ~ operator family:

Unlike LIKE and SIMILAR TO, regex patterns are not anchored. They match if the pattern exists anywhere in the string (like wrapping in %...%).

Find tracks containing "sun" case-insensitively:

This matches "Sunrise Protocol", "Sunrise Reprise", "Midnight Sun", and "Sunflower".

Find artists whose name starts with "The" followed by a space:

The ^ anchors to the start, \s matches any whitespace. This returns "The Midnight Signal", "The Spark", and "The Rolling Waves".

Practical Regex Patterns

The first query anchors gmail.com at the end with $ and escapes the dot (since . in regex means any character). The second uses ^ for start anchor, [a-z] for exactly one lowercase letter, then a literal underscore.

Regex vs LIKE: When to Use Each

Pattern matching has significant performance implications depending on the pattern structure.

Leading Wildcards Kill Index Usage

A B-Tree index stores values in sorted order. Prefix patterns ('Sun%') can navigate the tree efficiently because all matching values are adjacent in the sort order. But patterns starting with % could match values anywhere in the index, forcing a full table scan.

Alternatives for Substring Search

When you need %keyword% searches on large tables, consider these PostgreSQL alternatives:

These are advanced topics beyond what most interviews require, but knowing they exist demonstrates deeper understanding.