String & Date Functions, CASE Expressions

Reshaping values, not just filtering them

WHERE and ORDER BY decide which rows and in what order — string, date, and conditional functions decide what a value looks like once it's on its way out (or being compared). The four tools below cover the overwhelming majority of "reshape this column" interview questions.

String functions

SELECT
  CONCAT(first_name, ' ', last_name)           AS full_name,
  UPPER(last_name)                              AS shout_name,
  LOWER(email)                                  AS normalized_email,
  TRIM(BOTH ' ' FROM raw_input)                 AS cleaned_input,
  SUBSTRING(phone FROM 1 FOR 3)                 AS area_code,
  LENGTH(bio)                                   AS bio_length
FROM users;

CONCAT is the one function name that genuinely differs across engines in a way that bites people: standard SQL and PostgreSQL/MySQL support CONCAT(a, b, c) as a function, but the || operator (PostgreSQL, Oracle, SQLite) or + (older SQL Server) also concatenate — know which your target dialect expects, and default to the CONCAT() function form when unsure, since it's the most broadly portable and (critically) treats NULL arguments as empty strings on most engines rather than making the whole result NULL, which is what ||/+ typically do.

Pattern matching with LIKE

WHERE name LIKE 'A%'        -- starts with "A"
WHERE name LIKE '%son'      -- ends with "son"
WHERE name LIKE '%an%'      -- contains "an" anywhere
WHERE code LIKE '_-___'     -- exactly 5 chars: any char, literal "-", any 3 chars

% matches any sequence of characters (including zero); _ matches exactly one character. LIKE is case-sensitive on some engines (PostgreSQL) and case-insensitive by default on others (MySQL, SQL Server) — if a problem's expected output depends on case sensitivity, that's engine-specific behavior worth calling out explicitly rather than silently assuming. Most engines also support a genuinely case-insensitive variant (ILIKE in PostgreSQL) for when you want to guarantee it either way.

Date/time functions

SELECT
  EXTRACT(YEAR FROM order_date)                 AS order_year,
  DATE_TRUNC('month', order_date)                AS order_month,
  order_date + INTERVAL '7 days'                 AS one_week_later,
  AGE(NOW(), signup_date)                        AS account_age
FROM orders;

The two operations that come up constantly: extracting a part of a date (EXTRACT, or engine-specific YEAR()/MONTH()/DAY() functions in MySQL) for grouping-by-month/year problems, and date arithmetic (+ INTERVAL, DATEDIFF, DATE_ADD depending on engine) for "within N days of" filters. A frequent format-mismatch bug: a date stored as a VARCHAR (e.g., '2023-04-15' as plain text) needs an explicit CAST/TO_DATE/STR_TO_DATE before date functions will work correctly on it — comparing date-shaped strings lexicographically happens to work for ISO 8601 (YYYY-MM-DD) format specifically, but breaks silently for any other format (MM/DD/YYYY sorts nothing like a real date).

CASE expressions: turning a value into a bucket

SELECT
  employee_id,
  CASE
    WHEN salary < 50000  THEN 'Junior'
    WHEN salary < 100000 THEN 'Mid'
    ELSE 'Senior'
  END AS salary_band
FROM employees;

CASE evaluates its WHEN clauses top-to-bottom and stops at the first match — order matters when ranges overlap conceptually (as above: a salary of 40000 never reaches the second WHEN because the first already matched). Omitting ELSE makes every unmatched row NULL rather than raising an error, which is occasionally exactly what you want (see the "pivot with CASE inside an aggregate" pattern in Advanced Aggregation & Pivoting, where a NULL result is deliberately ignored by SUM/COUNT/MAX).

Why interviewers care

This is the layer where "I know the syntax" starts to matter noticeably less than "I can read the schema and predict edge cases" — a LIKE pattern that's subtly wrong, a date comparison that silently does string comparison instead, or a CASE with overlapping ranges in the wrong order are all bugs that produce a plausible-looking wrong answer rather than an error, which is exactly the failure mode interviewers are testing for when they ask you to "walk through what this returns on this specific row."

Pitfalls and interview gotchas

Comparing a text-typed date column with >/< without confirming it's actually stored as a date type. Lexicographic string comparison and chronological comparison only agree for ISO 8601-formatted strings.
Forgetting LIKE's case sensitivity is engine-dependent. State your assumption out loud if the problem doesn't specify the engine.
Writing overlapping CASE WHEN ranges in the wrong order, silently producing a value from the wrong bucket instead of erroring.
Using ||/+ for concatenation when any operand might be NULL, silently NULL-ing out the entire concatenated string instead of treating the NULL as empty.

Where this goes next

Joins & Multi-Table Queries (next) is where single-table fluency starts combining across tables — the point where the majority of realistic interview questions actually live, since almost no interesting business question is answerable from one table alone.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.

The Latest Login in 2020Easy!2/515m