LIKE & Wildcards (Pattern Matching)

= only finds exact matches, but real questions are fuzzier: "emails ending in @gmail.com", "products whose code starts with A", "names containing 'son'". LIKE with its two wildcards — % and _ — answers all of them.

Learn LIKE & Wildcards (Pattern Matching) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a…

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll master starts-with, ends-with, and contains searches, the single-character wildcard, NOT LIKE , escaping literal wildcards, and why some patterns are fast while others crawl.

A handful of users with varied names and email domains — perfect for pattern matching.

The % wildcard stands for "any run of characters, including none". Its position decides the kind of search: 'a%' is starts-with, '%a' is ends-with, and '%a%' is contains.

% is the autocomplete dropdown. Typing "joh" and getting "John", "Johnson", "Johanna" is exactly WHERE name LIKE 'joh%' .

The underscore _ matches precisely one character — no more, no less. It's perfect for fixed-format data like product codes, postal codes, or phone-number masks.

Replace the placeholder with a pattern that matches every name ending in "son".

NOT LIKE flips the test — it returns rows that do not match. And when you need to search for a literal % or _ (not as wildcards), an ESCAPE character tells SQL to treat the next symbol as plain data.

Unscramble these lines into a query that finds users whose email starts with "j" but is not a @shop.com address.

Why: SELECT and FROM come first, then a single WHERE holds both conditions joined by AND . The first LIKE anchors the start ('j%'); the second uses NOT LIKE to exclude the shop domain. In our data only "john@shop.com" starts with j — and it's excluded — so the result is empty, a useful reminder to check your assumptions.

Predict each result, then reveal. Use the users table.

3 — Maria, Omar and Emma all have gmail addresses.

Yes — S, then exactly one char (a), then r, then anything (a Lin). The pattern fits "Sara Lin".

'A%' matches "A" (% allows zero chars), but 'A_' does not — the underscore demands one more character after A.

Find Gmail users whose name contains the letter "a". Combine two LIKE conditions. Expected shape is in the comments.

Practice quiz

What does the % wildcard match in a LIKE pattern?

  • Exactly one character
  • Only digits
  • Any sequence of zero or more characters
  • Only letters

Answer: Any sequence of zero or more characters. % matches any run of characters, including none.

What does the _ wildcard match?

  • Exactly one character
  • Zero or more characters
  • A literal underscore only
  • A whole word

Answer: Exactly one character. _ matches precisely one character — no more, no less.

Which pattern finds values that START WITH 'a'?

  • '%a'
  • '%a%'
  • '_a'
  • 'a%'

Answer: 'a%'. The % at the end means 'a' followed by anything — a starts-with search.

Which pattern finds values that CONTAIN 'mid' anywhere?

  • 'mid%'
  • '%mid%'
  • '%mid'
  • 'mid'

Answer: '%mid%'. Wrapping the term in % on both sides gives a contains search.

For the value 'A', which is true?

  • LIKE 'A%' matches it but LIKE 'A_' does not
  • Both match it
  • Neither matches it
  • Only LIKE 'A_' matches it

Answer: LIKE 'A%' matches it but LIKE 'A_' does not. % allows zero characters so 'A%' matches 'A'; '_' demands one more character, so 'A_' does not.

What does NOT LIKE do?

  • Matches only exact strings
  • Negates the wildcards into literals
  • Returns rows that do NOT match the pattern
  • Is a syntax error

Answer: Returns rows that do NOT match the pattern. NOT LIKE returns the rows that fail the pattern.

How do you search for a LITERAL % sign in LIKE?

  • Wrap it in quotes twice
  • Use an ESCAPE character, e.g. LIKE '50\%%' ESCAPE '\'
  • Use _ instead
  • It is impossible

Answer: Use an ESCAPE character, e.g. LIKE '50\%%' ESCAPE '\'. An ESCAPE character tells SQL to treat the following % as data, not a wildcard.

Why is a leading-wildcard search like '%term' slow?

  • It returns too many rows
  • % is invalid at the start
  • It locks the table
  • It can't use a B-tree index, so it scans every row

Answer: It can't use a B-tree index, so it scans every row. A pattern starting with % can't use the sorted index; the database must scan the whole table.

Which LIKE pattern CAN use a normal index?

  • '%term' (leading wildcard)
  • 'term%' (anchored at the start)
  • '%term%'
  • '%t%m%'

Answer: 'term%' (anchored at the start). Patterns anchored at the start can use an index; leading wildcards cannot.

Does 'a_c' match the value 'abbc'?

  • Yes — _ matches any number of chars
  • Yes — it ignores length
  • No — _ is exactly one char, so 'a_c' needs 3 chars total
  • No — because it must start with b

Answer: No — _ is exactly one char, so 'a_c' needs 3 chars total. 'a_c' matches three-character strings like 'abc' or 'axc', not the four-character 'abbc'.