String & date functions: cleaning and slicing the messy columns
Real data is full of messy text and dates. A handful of built-in functions (LOWER, TRIM, DATE_TRUNC, EXTRACT) cover most of what an analyst does day to day.
Real-world columns are messy: names with odd capitalisation, extra spaces, dates you need to group by month. SQL has small built-in functions to clean and slice these. You don't need to memorise all of them, just know the handful you'll reach for constantly.
🎯 Explain Like I'm Hired String and date functions are your cleanup and slicing tools. String functions fix messy text (lowercasing, trimming spaces, chopping out part of a value). Date functions let you zoom a date in or out, turning an exact timestamp into "which month was this?" so you can group by it. Example: asked for "revenue by month," you'd use a date function (
DATE_TRUNC('month', placed_at)) to flatten every order's exact time down to its month, then group by that.
The string ones you'll actually use
SELECT
LOWER(email) AS email_clean, -- 'RIYA@X.COM' -> 'riya@x.com'
TRIM(name) AS name_clean, -- remove leading/trailing spaces
CONCAT(first, ' ', last) AS full_name, -- glue values together
SUBSTRING(phone, 1, 3) AS area_code -- first 3 characters
FROM customers;
LOWER/UPPER change case (great for making comparisons consistent), TRIM removes stray spaces,
CONCAT glues text together, SUBSTRING pulls out part of a value.
The date ones you'll actually use
SELECT
DATE_TRUNC('month', placed_at) AS month, -- flatten to the 1st of the month
EXTRACT(YEAR FROM placed_at) AS year, -- pull out just the year
placed_at + INTERVAL '7 days' AS due_date -- date math
FROM orders;
DATE_TRUNC('month', ...) is the workhorse: it turns any exact timestamp into the start of its
month, so all of January's orders share one value you can group by. EXTRACT pulls out one piece (the
year, the hour). Adding an INTERVAL does date arithmetic.
The interview tip
"Group sales by month" is one of the most common tasks you'll be handed. It's just DATE_TRUNC plus
GROUP BY:
SELECT DATE_TRUNC('month', placed_at) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;