LIKE

Hook: Interviewers love LIKE because it looks tiny, but it hides wildcard rules, escaping, and whether your query can use an index.

Question: What does LIKE do in SQL?

Answer: LIKE filters rows by matching a text pattern instead of an exact value. The two main wildcards are % for “zero or more characters” and _ for “exactly one character.” If you need to search for a literal % or _, you use an ESCAPE character.

Interview-Ready Answer: I use LIKE when I need pattern matching in a WHERE clause. % matches any length of text, _ matches exactly one character, and if I need the symbols themselves I escape them with ESCAPE. One important detail is that case sensitivity and index usage depend on the database and the pattern, especially whether the pattern starts with a wildcard.

What LIKE is really doing

LIKE compares a column value to a pattern. Think of the pattern as a shape the text must fit. It is not regex, and it is not fuzzy search; it is a simple wildcard matcher that SQL engines can optimize in some cases.

How it works, step by step

1. The database reads the pattern string, for example 'Al%'.
2. It compares the column value from left to right.
3. % can absorb any sequence of characters, including an empty sequence.
4. _ can absorb exactly one character.
5. If an ESCAPE character is declared, the next character is treated literally, so !_ means a real underscore instead of the one-character wildcard.
6. If the column value is NULL, the result is not true; it is unknown, so the row does not pass the filter.

When to use it

• Prefix searches: name LIKE 'Ali%'.
• Simple code formats: order_id LIKE 'ORD-2024%'.
• Finding values that contain a small token: email LIKE '%@gmail.com' when a scan is acceptable.

Comparing common pattern styles

Performance and under-the-hood notes

Without an index, LIKE is usually a table scan: the engine checks each row, so the work grows roughly with the number of rows, O(N). With a B-tree index, a prefix pattern like 'abc%' can often become a range search, closer to O(log N + K), where K is the number of matches. On a 10 million row table, that difference can mean milliseconds versus seconds.

The big gotcha is a leading wildcard: '%abc' usually prevents the engine from jumping into the index, because it does not know where the match starts. Another gotcha is collation, which is the set of rules a database uses for comparing text. In some databases LIKE is case-sensitive; in others it follows a case-insensitive collation. PostgreSQL keeps LIKE case-sensitive and offers ILIKE as a case-insensitive extension.

Memory check

Remember: % is “any length,” _ is “one seat,” and ESCAPE says “this symbol is real, not magic.”

Real-World Example: Imagine a checkout service for an online store that stores order codes like ORD-2024-00123. Product support needs to search all orders from a year, so the app runs WHERE order_code LIKE 'ORD-2024%'. That prefix pattern is a good fit because it is predictable and can often use an index.

Now imagine a bug: a developer wants to find a literal promo code such as VIP_20 but writes LIKE '%VIP_20%'. The underscore becomes a wildcard, so the query returns codes like VIPA20, VIP-20, and other near matches. In production, support agents see the wrong customer records, logs show a much larger result set than expected, and the database may slow down if the query scans millions of rows. The fix is to escape the underscore and, if the lookup is common, pair the search pattern with a good index-friendly prefix.

Follow-up & Tricky Questions:

• How is LIKE different from =? = checks one exact value, while LIKE allows wildcards. If there are no wildcards, many databases can still treat LIKE 'abc' like equality, but you should not rely on it for intent.
• Can LIKE use an index? Yes, often for prefix searches such as 'abc%'. It usually cannot use a normal B-tree index efficiently when the pattern starts with %.
• What does % match? Zero or more characters, including an empty string. That is why 'a%' matches 'a' and 'abc'.
• What does _ match? Exactly one character. It is great for fixed-length formats, but it is easy to overmatch if the text has variable length.
• What happens with NULL? LIKE returns unknown, not true, so the row is filtered out. If you want to keep nulls, you must check IS NULL separately.

• Tricky: Is LIKE case-insensitive? Not universally. It depends on the database and collation; for example, PostgreSQL LIKE is case-sensitive, while MySQL often depends on the column collation.
• Tricky: Is LIKE '%abc%' a good search pattern? It is fine for small tables or rare admin tools, but it often forces a scan on large tables. For user search at scale, you usually want a different strategy.
• Tricky: Does LIKE 'abc%' always use the same plan? No. The index, collation, data distribution, and database engine all matter. The pattern is index-friendly, but the optimizer still decides whether to use that index.

Common Mistakes:

• Forgetting that _ is a wildcard. Correction: escape it with ESCAPE when you need the literal character.
• Using leading wildcards everywhere. Correction: prefer prefix patterns like 'abc%' when possible so indexes can help.
• Assuming case behavior is the same in every database. Correction: check the engine and collation; do not guess.
• Ignoring NULL. Correction: LIKE does not match null values, so add IS NULL logic when needed.

Memory Hook: Think of LIKE as a fishing net: % is the big net for any length, _ is one single hook, and ESCAPE tells the database, “this symbol is a real fish, not part of the net.”

Cheat Sheet:

• % = zero or more characters.
• _ = exactly one character.
• ESCAPE makes wildcards literal.
• LIKE 'abc%' is usually index-friendly.
• LIKE '%abc' usually scans.
• NULL never matches.

Practice Tasks:

• Write a query that finds all emails ending with @gmail.com.
• Write a query that matches product codes like SKU-1234 using _ for the digits.
• Write a query that finds a literal value containing % or _ without accidentally treating them as wildcards.