0Pricing
SQL Interview Prep · Lesson

LIKE, Wildcards and Escaping

Pattern matching, percent vs underscore, and escaping literal wildcard characters.

LIKE, Wildcards and Escaping is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Pattern Matching Under Interview Scrutiny

LIKE looks simple but hides three classic interview gotchas: confusing % with _, forgetting that a literal percent or underscore in the data needs escaping, and the case-sensitivity surprise that differs by database.

This lesson drills the two wildcards, the ESCAPE clause, and the cross-dialect rules an interviewer will probe to separate juniors from people who have shipped real search features.

The Two Wildcards

LIKE has exactly two wildcard characters:

  • % matches zero or more characters
  • _ matches exactly one character

So 'a%' matches anything starting with a, while 'a_' matches a two-character string starting with a. Mixing these up is the number one LIKE mistake.

SELECT *
FROM customers
WHERE name LIKE 'A%';
-- every name beginning with A

Percent Anywhere

Position the % to control where the match anchors:

  • '%son' ends with son (Johnson, Mason)
  • '%mary%' contains mary anywhere
  • 'J%n' starts with J and ends with n

A bare '%' matches every non-NULL row. NULLs never match any LIKE pattern, including '%'.

SELECT *
FROM customers
WHERE email LIKE '%@gmail.com';

Underscore for Fixed Length

_ matches a single arbitrary character, useful for fixed-width codes. 'A__' matches exactly three characters starting with A, no more, no less.

Interviewers test this with product codes or country codes. The candidate who writes 'A%' when asked for a three-character code that starts with A gets it subtly wrong, returning A-anything.

SELECT *
FROM products
WHERE sku LIKE 'A__';
-- A followed by exactly two more characters

When % and _ Appear in the Data

What if you need to find values that literally contain a percent sign, like a discount label '50% off'? Writing LIKE '%50%%' fails, because the trailing % is treated as a wildcard, not a literal.

You must tell SQL that one specific character is literal. That is what the ESCAPE clause is for.

The ESCAPE Clause

Declare an escape character with ESCAPE, then prefix any wildcard you want treated literally. Here ! is the escape char, so !% means a literal percent sign.

This finds values containing the literal text 50% anywhere. The escape character is arbitrary; pick one that does not appear in your search text.

SELECT *
FROM promos
WHERE label LIKE '%50!%%' ESCAPE '!';
-- matches a literal '50%' anywhere in label

Escaping a Literal Underscore

Underscores hide in usernames and identifiers, and an un-escaped _ quietly matches any character. To find emails that literally start with 'a_b' you must escape the underscore.

Without the escape, 'a_b%' would also match 'axb', 'a9b', and so on. This bug ships to production constantly because the un-escaped query still returns plausible-looking rows.

SELECT *
FROM users
WHERE handle LIKE 'a!_b%' ESCAPE '!';
-- handle starting with the literal text a_b

Case Sensitivity Varies by Database

Whether LIKE is case-sensitive depends on the engine and collation:

  • PostgreSQL: LIKE is case-sensitive; use ILIKE for case-insensitive
  • MySQL: depends on column collation, often case-insensitive by default
  • SQL Server: depends on the collation setting

The portable, explicit approach is to lower-case both sides.

SELECT *
FROM customers
WHERE LOWER(name) LIKE 'a%';

The Hidden Performance Cost

A leading wildcard like '%son' cannot use a normal B-tree index, because the index is ordered by prefix and you have not anchored the start. The engine must scan every row.

'son%' (trailing wildcard, anchored prefix) can use an index. Interviewers love asking why a LIKE search is slow; the leading % is usually the answer.

-- index-friendly (anchored prefix):
WHERE name LIKE 'Smi%'
-- forces a scan (leading wildcard):
WHERE name LIKE '%mith'

Beyond LIKE

When LIKE is not enough, mention the alternatives to signal depth:

  • SIMILAR TO and POSIX regex (~, ~*) in PostgreSQL for true regular expressions
  • Full-text search indexes for large free-text corpora
  • Trigram indexes (pg_trgm) to make leading-wildcard searches fast

Naming these shows you know LIKE is the floor, not the ceiling, of text search.

Building LIKE Patterns From User Input Safely

Interviewers often ask: how do you search for a user-typed term with LIKE without breaking? Two risks to call out:

  • Injection — bind the value as a parameter; never concatenate raw input into the SQL string.
  • Unescaped wildcards — if the input itself contains % or _, escape them so they match literally.

Bind the parameter, then add your own wildcards in the query.

-- $1 is bound as a parameter; wildcards are added in SQL
SELECT *
FROM products
WHERE name LIKE '%' || replace(replace($1, '_', '\_'), '%', '\%') || '%' ESCAPE '\';

Quick Check

Recall the difference between the two wildcards.

Recap

Key takeaways:

  • % matches zero or more characters; _ matches exactly one
  • To match a literal % or _ in data, declare an ESCAPE character and prefix the wildcard
  • Case sensitivity varies by engine; LOWER() on both sides is the portable fix (Postgres has ILIKE)
  • A leading wildcard forces a full scan; an anchored prefix can use an index

An un-escaped _ is a silent bug: it still returns plausible rows.

Frequently asked questions

Is the “LIKE, Wildcards and Escaping” lesson free?

Yes — the full text of “LIKE, Wildcards and Escaping” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “LIKE, Wildcards and Escaping”?

Pattern matching, percent vs underscore, and escaping literal wildcard characters. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LIKE, Wildcards and Escaping” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. AND/OR Precedence and Parenthesization
  2. BETWEEN, IN, and Inclusive Boundaries
  3. LIKE, Wildcards and Escaping
  4. Filtering on Calculated Values
← Back to SQL Interview Prep