Built-In Scalar Functions

Scalar means one value per row#

Unlike aggregates, scalar functions run on each row and return one value for that row.

String functions#

SELECT CONCAT(name, ' <', email, '>')        AS contact,
       UPPER(city)                           AS city_uc,
       SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username,
       LENGTH('héllo') AS bytes, CHAR_LENGTH('héllo') AS chars,
       TRIM('  x  ')  AS trimmed
FROM customers;

LENGTH counts bytes and CHAR_LENGTH counts characters. For anything non-ASCII, only CHAR_LENGTH gives the number humans mean. CONCAT returns NULL if any argument is NULL. Use CONCAT_WS(', ', a, b) to skip NULLs.

Date and time functions#

SELECT NOW(), CURDATE(),
       DATE_FORMAT(ordered_at, '%d %b %Y')       AS pretty,
       TIMESTAMPDIFF(DAY, ordered_at, NOW())     AS age_days,
       DATE_ADD(ordered_at, INTERVAL 7 DAY)      AS due,
       YEAR(ordered_at), MONTH(ordered_at)
FROM orders;

TIMESTAMPDIFF(unit, start, end) gives whole units between two points. Argument order matters, the earlier date comes first.

Dates in WHERE: keep columns bare#

-- Slow: function on the column, index can't be used
WHERE YEAR(ordered_at) = 2025

-- Fast: range on the raw column
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'

Wrapping an indexed column in a function usually stops MySQL using the index. Rewrite as a range.

Conditional logic#

SELECT name,
       CASE
         WHEN stock = 0   THEN 'out'
         WHEN stock < 20  THEN 'low'
         ELSE 'ok'
       END AS stock_level,
       IF(price > 500, 'premium', 'standard') AS tier
FROM products;

SELECT name, IFNULL(city, 'unknown') AS city_safe,
       COALESCE(city, 'n/a') AS place
FROM customers;

CASE evaluates conditions top to bottom and stops at the first match. COALESCE returns the first non-NULL argument and takes any number of them. IFNULL is its two-argument form.

Conditional aggregation is one of the most useful tricks:

SELECT customer_id,
       SUM(status = 'paid')    AS paid_orders,
       SUM(status = 'new')     AS new_orders
FROM orders GROUP BY customer_id;

Comparisons return 1 or 0 in MySQL, so SUM counts the true ones.

Common mistakes#

• LENGTH on UTF-8 text when you meant CHAR_LENGTH.
• YEAR(col) = 2025 on a big indexed table.
• CONCAT swallowing a NULL and producing NULL.
• Forgetting a CASE ELSE, which silently gives NULL.

Practice#

Write a query listing each order with its age in days, a label (recent if under 30 days, old otherwise), and the customer's name with city, falling back to 'unknown'. Then rewrite a "orders placed in February 2025" filter so it can use an index on ordered_at.