String handling
SQL · 5 interview questions
String problems in SQL interviews are usually really about knowing which function exists in which dialect, and about the difference between pattern matching and regular expressions.
LIKE handles simple patterns with % for any run of characters and _ for exactly one. It's the portable choice. REGEXP (or ~ in Postgres) handles real regular expressions and is what you want for something like validating an email shape — but anchoring matters: an unanchored pattern matches anywhere in the string, so ^ and $ are usually required rather than optional.
Concatenation is a genuine portability trap. CONCAT(a, b) works nearly everywhere; a || b is standard SQL and works in Postgres and SQLite but means logical OR in MySQL unless a specific mode is set. If a table might contain NULLs, remember that concatenating NULL usually produces NULL — CONCAT_WS and COALESCE exist for that reason.
Case sensitivity depends on collation, not on the function you call. MySQL's default collations are case-insensitive, so WHERE name = 'alice' matches 'Alice' — which surprises people arriving from Postgres, where it doesn't. When correctness depends on it, normalise explicitly with UPPER or LOWER rather than relying on the server's configuration.
String handling interview questions
- What do % and _ mean in a LIKE pattern?
%matches any sequence of characters including none;_matches exactly one character. To match a literal % or _ you escape it. LIKE is not a regular expression —REGEXPis, and it's a different operator.- Why they ask: The
_half is the part people forget exists. - How do you capitalise only the first letter of a name?
- Combine substring extraction with case functions:
CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))). There's no portable single function — Postgres has INITCAP, MySQL doesn't. - Why they ask: A common problem whose answer is 'assemble it yourself', which people don't expect.
- What's the portable way to concatenate strings?
CONCAT(a, b). The||operator is standard SQL and works in Postgres and SQLite, but in MySQL it means logical OR unless PIPES_AS_CONCAT is set. Also note that concatenating a NULL usually yields NULL — use CONCAT_WS or COALESCE to avoid it.- Why they ask: A portability trap with a silent failure mode in MySQL.
- When matching a pattern with REGEXP, why do you usually need ^ and $?
- Without anchors the pattern matches anywhere inside the string, so a 'valid email' check would accept anything merely containing a valid-looking substring.
^and$force the match to span the whole value. - Why they ask: The difference between a validation that works and one that appears to.
- Is string comparison case-sensitive in SQL?
- It depends on collation, not on the query. MySQL's default collations are case-insensitive, so
= 'alice'matches 'Alice'. Postgres is case-sensitive by default. When it matters, normalise explicitly with UPPER or LOWER instead of trusting the server's configuration. - Why they ask: 'It depends on collation' is the answer that shows real experience.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.