A scalar function takes a value and returns a value, once per row, unlike an aggregate which works across rows.
Strings. CONCAT joins - and returns NULL if any argument is NULL, which CONCAT_WS avoids. SUBSTRING extracts, counting from 1 rather than 0. LENGTH returns bytes and CHAR_LENGTH returns characters, which differ the moment the text is not ASCII. REPLACE, TRIM, UPPER and LOWER do what they say.
Numbers. ROUND to a number of places, CEIL and FLOOR to whole numbers up and down, ABS for magnitude, MOD for the remainder. ROUND on a DECIMAL behaves predictably; on a FLOAT it inherits the floating-point surprises.
Dates. NOW() is the moment including time, CURDATE() is today's date. DATEDIFF gives whole days between two dates, TIMESTAMPDIFF gives the difference in any unit you name, and DATE_ADD and DATE_SUB do interval arithmetic. DATE_FORMAT renders a date as text - and should mostly be left to the application, since a date formatted in SQL can no longer be sorted or compared as a date.
The habit that matters: a function wrapped around an indexed column stops the index being used. WHERE YEAR(placed_at) = 2026 cannot use an index on placed_at, because the index is sorted by the column, not by the function of it. Rewrite it as a range - WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01' - and the same query becomes a seek. This single rewrite accounts for an enormous share of real-world query speed-ups.
-- CONCAT returns NULL if ANY argument is NULL. CONCAT_WS skips them.
SELECT CONCAT(name, ' <', email, '>') AS contact,
CONCAT_WS(', ', name, city, country) AS address,
SUBSTRING(reference, 1, 3) AS prefix, -- counts from 1
LENGTH('café') AS bytes, -- 5
CHAR_LENGTH('café') AS chars, -- 4
UPPER(city) AS shouty
FROM customers;
SELECT ROUND(total, 2), CEIL(total), FLOOR(total), ABS(balance), MOD(id, 10)
FROM orders;
SELECT NOW(), CURDATE(),
DATEDIFF(NOW(), placed_at) AS days_ago,
TIMESTAMPDIFF(MONTH, placed_at, NOW()) AS months_ago,
DATE_ADD(placed_at, INTERVAL 30 DAY) AS due,
DATE_FORMAT(placed_at, '%d %b %Y') AS printed
FROM orders;
-- Conditional logic, row by row.
SELECT reference,
CASE
WHEN total >= 1000 THEN 'large'
WHEN total >= 100 THEN 'medium'
ELSE 'small'
END AS band,
IF(status = 'paid', 'yes', 'no') AS settled,
IFNULL(notes, 'none') AS note
FROM orders;
-- THE habit. A function around the column defeats the index on it.
-- SELECT * FROM orders WHERE YEAR(placed_at) = 2026; -- scan
SELECT * FROM orders -- seek
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01';