SQL String Functions: The Interview Guide

The Questions Hiding in "Data Cleaning"

SQL interviews love dressing string functions up as business problems: "Marketing wants all users with a company email", "standardize these phone numbers", "list each customer's products in one cell". Underneath, it's the same five functions every time. Master them and this entire question category becomes free points.

LIKE and Pattern Matching

The workhorse for filtering text:

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

% matches any sequence of characters, _ matches exactly one. Interview traps to know: LIKE is case-sensitive in some databases (Postgres) but not others (MySQL) — mention ILIKE or LOWER() for case-insensitive matching, and know that a leading wildcard ('%...') defeats indexes, which is a common follow-up at companies that care about scale. Practice: Filter with LIKE Pattern.

LENGTH and SUBSTRING

Slicing and measuring strings powers a surprising number of questions — extracting area codes, first initials, or domain names:

SELECT
    LENGTH(name) AS name_length,
    SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS username
FROM users;

The composable pattern — POSITION to find a character, SUBSTRING to cut around it — solves most "extract the part before/after X" questions without regex. Practice: String Length and Substring.

REPLACE and CONCAT for Cleaning

"Phone numbers were entered as (555) 123-4567, 555.123.4567, and 5551234567. Standardize them."

SELECT REPLACE(REPLACE(REPLACE(REPLACE(phone,
    '(', ''), ')', ''), '-', ''), '.', '') AS clean_phone
FROM contacts;

Nested REPLACE calls read ugly but appear in real interviews constantly. Its partner is concatenation — CONCAT(first_name, ' ', last_name) or the || operator — with the classic follow-up about NULL behavior: in standard SQL, NULL || 'x' is NULL, while CONCAT treats NULL as an empty string in most engines. Practice: REPLACE and CONCAT for Data Cleaning.

STRING_AGG: The Modern Favorite

The question format that's exploded in popularity: "Show each customer with a comma-separated list of the products they've bought."

SELECT customer_id,
       STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM orders
GROUP BY customer_id;

STRING_AGG (Postgres/SQL Server) — GROUP_CONCAT in MySQL — collapses rows into one string per group. Knowing you can ORDER BY inside the aggregate is the detail that impresses. Practice: String Aggregation and Grouping.

Quick Reference

Practice

All four SQL string function problems run directly in your browser with real result checking — the fastest way to make these patterns automatic before an interview.