skip to content

In a SQL LIKE pattern, what do the % and _ wildcards each match?

level: juniorimportance: must knowfreq 85%

answer

  1. two metacharacters, everything else literal
  2. one is fixed width, one is not
  3. zero-or-more versus exactly-one
  4. the pattern must cover the whole value

basics

~20 s

In a LIKE pattern, % matches any sequence of zero or more characters and _ matches exactly one character. Every other character is a literal, and the pattern must match the whole value, not just part of it.

solid answer

~40 s

`LIKE` compares a string against a pattern built from two metacharacters: `%` matches any sequence of characters including the empty sequence, and `_` matches exactly one character. Everything else in the pattern is literal text. The pattern is matched against the **entire** value, so `'abc' LIKE 'b'` is false and you need `'%b%'` for a contains-search. That gives the three everyday shapes: `'abc%'` for starts-with, `'%abc'` for ends-with, `'%abc%'` for contains. Because `%` also matches zero characters, `'abc' LIKE 'abc%'` is true, while `'abc' LIKE 'a_'` is false — `_` demands exactly one character, no more, no fewer. Prefix `NOT` (`col NOT LIKE 'test%'`) to negate the predicate.

code

sql · 11 lines
sql
SELECT name
FROM   products
WHERE  name LIKE 'Pro%';   -- 'Pro', 'Product X'  -> match; 'Repro' -> no match

SELECT name
FROM   products
WHERE  name LIKE '%Pro%';  -- 'Repro' -> match; the contains form

SELECT code
FROM   products
WHERE  code LIKE 'A_-____'; -- 'AX-1234' -> match; 'A-1234' -> no match

go deeper

for a junior

Be ready to define both wildcards precisely and to write starts-with, ends-with and contains patterns on the spot. Remember the pattern is compared to the whole value.

for a middle

Explain the mechanics: % matches the empty sequence, _ is exactly one character, and a pattern of literals with no wildcards behaves like equality. Know how underscores pin down fixed-width formats.

for a senior

Show judgment about filter correctness in production: negated patterns, whether a search should be anchored as a prefix or a contains, and the fact that case behaviour is a collation decision rather than a property of LIKE.

for a principal

Own the consistency angle: search semantics that differ between screens, or between engines with different collation defaults, become user-visible inconsistency. Decide where pattern-building lives and how it is tested.

## What the LIKE predicate is `LIKE` is a comparison predicate for character strings. Its shape is: ```sql <string expression> [NOT] LIKE <pattern> [ESCAPE <single character>] ``` It yields TRUE, FALSE, or UNKNOWN, and only rows for which a `WHERE` predicate is TRUE come back. (If either the value or the pattern is NULL the result is UNKNOWN, so the row is not returned — that is the general NULL rule, not something special about `LIKE`.) ## The two wildcards A pattern is ordinary text plus exactly two metacharacters: - `%` matches **any sequence of zero or more characters**. It is happy to match nothing at all. - `_` matches **exactly one character** — any character, including a space or a digit, but precisely one. Everything else in the pattern stands for itself. There is no `*`, no `?`, no `.` and no `+` in `LIKE`: those belong to regular-expression operators, which are a different feature. ## The pattern covers the whole value This is the single most common misunderstanding. `LIKE` is not a substring search: the pattern is matched against the **entire** string, as if it were anchored at both ends. So: ```sql -- with the value 'abc' 'abc' LIKE 'abc' -- TRUE (no wildcards: an exact match) 'abc' LIKE 'b' -- FALSE (not a substring search) 'abc' LIKE '%b%' -- TRUE (this is how you say "contains b") ``` That is why every "contains" filter carries wildcards on both sides. ## Worked examples Against the value `'abc'`: - `'a%'` → TRUE. Literal `a`, then anything. - `'%c'` → TRUE. Anything, then a literal `c`. - `'a_c'` → TRUE. `a`, exactly one character, `c`. - `'a_'` → FALSE. That pattern describes a two-character string. - `'abc%'` → TRUE. `%` matched the empty sequence, so a prefix pattern always matches the prefix itself. - `'_%'` → TRUE for any value of at least one character; FALSE for the empty string. - `'%'` → TRUE for every non-NULL string, including `''`. ## The three shapes you write every day ```sql WHERE email LIKE 'admin%' -- starts with WHERE file LIKE '%.pdf' -- ends with WHERE title LIKE '%report%' -- contains ``` `_` is for fixed-width formats: `WHERE code LIKE 'A_-____'` describes a code whose first character is `A`, then one character, then a hyphen, then four characters. It is the right tool when position matters and length is known. ## NOT LIKE `NOT LIKE` is the plain negation of the predicate: `WHERE name NOT LIKE 'test%'` keeps the rows whose name does not begin with `test`. Watch the wildcard placement when you negate — `NOT LIKE '%test%'` means "does not contain test", which is a much broader filter than "does not start with test", and mixing the two up is a frequent bug in exclusion filters. ## Edges worth naming out loud - **Case.** Whether `'ABC' LIKE 'a%'` is true depends on the collation of the compared strings, and engines ship different defaults. Never assume. - **Literal wildcards.** If the text you are searching for genuinely contains `%` or `_`, you must neutralise it with an `ESCAPE` character; otherwise it is read as a wildcard. - **Multiple wildcards.** `'%a%b%'` is legal and means "an `a` somewhere, then a `b` somewhere after it". Consecutive `_` characters simply count off positions: `'____'` is any four-character string. - **Empty pattern.** `'' LIKE ''` is TRUE; `'x' LIKE ''` is FALSE. ## What an interviewer is listening for They want to hear the two wildcards defined precisely — especially that `%` includes the empty match and `_` is exactly one, not "one or more" — and they want to hear that the pattern is matched against the whole value. Candidates who describe `LIKE` as "a substring search" usually go on to write `LIKE 'term'` and wonder why nothing comes back.

  • Does 'abc' LIKE 'abc%' return true, and why does that matter?
    Yes. `%` matches a sequence of zero or more characters, so it is satisfied by the empty sequence and a prefix pattern always matches the prefix itself. It matters for filters such as `path LIKE '/docs%'`, which correctly includes the exact value `'/docs'` as well as everything beneath it, so you do not need a separate equality test.
  • How would you write a filter for a code that is exactly five characters and starts with a letter A?
    `WHERE code LIKE 'A____'` — the literal `A` plus four `_` placeholders, each matching exactly one character. Because the pattern must match the whole value, this also enforces the total length of five; no separate length check is needed. Use `%` instead of the underscores only if you do not care about the length.
  • Why does WHERE title LIKE 'report' return nothing even though many titles contain the word report?
    `LIKE` matches the pattern against the entire value, not a substring of it, so `'report'` with no wildcards behaves like `=` and only matches a title that is exactly `report`. For a contains-search you need wildcards on both sides: `LIKE '%report%'`. For starts-with, `LIKE 'report%'`.

saying these in an interview costs you the question

  • Calling LIKE a substring search and writing LIKE 'term' with no wildcards
  • Saying _ matches one or more characters
  • Claiming % needs at least one character to match
  • Using regex metacharacters such as * or . in a LIKE pattern
  • Assuming LIKE is always case-insensitive

context

resolving links…