Chapter 9
Chapter 9 — String, Date, and Numeric Functions
MySQL has hundreds of built-in functions. This chapter covers the ones you'll use every week — functions for cleaning and reshaping strings, working with dates and times, and performing precise numeric calculations. Each section follows the same pattern: a quick-reference card of the core functions, then practical bookshop queries showing how they compose.
Functions go in the SELECT list, WHERE clause, ORDER BY, and ON conditions.
They can be nested, combined with operators, and aliased with AS. Anywhere you can
write a column name, you can write a function call that produces a value.
Part 1 — String Functions
Core string functions
| CONCAT(a, b, …) | Join two or more strings. Returns NULL if any argument is NULL — use CONCAT_WS to avoid this. | CONCAT('Matt', ' ', 'Haig') → 'Matt Haig' |
| CONCAT_WS(sep, a, b, …) | Concat With Separator. Skips NULL arguments (unlike CONCAT). The separator is the first argument. | CONCAT_WS(' ', 'Matt', NULL, 'Haig') → 'Matt Haig' |
| LENGTH(str) | Number of bytes in the string. For multi-byte characters (emoji, accents), use CHAR_LENGTH instead. | LENGTH('Dune') → 4 |
| CHAR_LENGTH(str) | Number of characters (not bytes). Safe for Unicode — 'café' is 4 chars, 5 bytes in utf8mb4. | CHAR_LENGTH('café') → 4 |
| UPPER(str) / LOWER(str) | Convert to upper or lower case. Respects the column's collation. | UPPER('dune') → 'DUNE' |
| TRIM(str) | Remove leading and trailing spaces. LTRIM removes only left spaces, RTRIM only right. | TRIM(' Dune ') → 'Dune' |
| SUBSTRING(str, pos, len) | Extract a portion of a string. Position is 1-based. Omit len to go to end of string. Also: SUBSTR, MID. | SUBSTRING('Dune', 1, 2) → 'Du' |
| LEFT(str, n) / RIGHT(str, n) | First or last n characters. Cleaner than SUBSTRING when you just want the start or end. | LEFT('Sapiens', 3) → 'Sap' |
| REPLACE(str, from, to) | Replace all occurrences of a substring. Case-sensitive. Returns the modified string. | REPLACE('Sci-fi', '-', '/') → 'Sci/fi' |
| LOCATE(needle, haystack) | Position of the first occurrence of needle in haystack. Returns 0 if not found. Case-insensitive by default. | LOCATE('mid', 'Midnight') → 1 |
| LPAD(str, len, pad) / RPAD | Left or right pad a string to a given total length with the pad character. Useful for formatting IDs. | LPAD('5', 4, '0') → '0005' |
| REPEAT(str, n) | Repeat a string n times. | REPEAT('★', 3) → '★★★' |
| REVERSE(str) | Reverse a string character by character. | REVERSE('Dune') → 'enuD' |
Practical string queries
-- Full name as a single string, title-truncated to 20 chars
SELECT
CONCAT(a.first_name, ' ', a.last_name) AS author_name,
LEFT(b.title, 20) AS short_title,
CHAR_LENGTH(b.title) AS title_length
FROM books b
JOIN authors a ON b.author_id = a.author_id
ORDER BY title_length DESC;
| author_name | short_title | title_length |
|---|---|---|
| Ursula Le Guin | The Left Hand of Dark | 25 |
| Agatha Christie | And Then There Were | 24 |
| Matt Haig | The Midnight Library | 20 |
| Matt Haig | Reasons to Stay Aliv | 20 |
| Yuval Harari | Sapiens | 7 |
| Frank Herbert | Dune | 4 |
-- Search for titles containing a word, case-insensitively
SELECT title
FROM books
WHERE LOCATE('the', LOWER(title)) > 0;
-- Pad order IDs to 6 digits for a reference number
SELECT
CONCAT('ORD-', LPAD(order_id, 6, '0')) AS reference,
total_price
FROM orders;
| reference | total_price |
|---|---|
| ORD-000001 | 17.98 |
| ORD-000002 | 10.99 |
| ORD-000003 | 9.99 |
| ORD-000004 | 20.97 |
| ORD-000005 | 7.99 |
-- Clean up messy email addresses from a data import
UPDATE customers
SET email = LOWER(TRIM(email));
-- Replace a genre label that was entered inconsistently
UPDATE books
SET genre = REPLACE(genre, 'Sci-fi', 'Science Fiction')
WHERE genre = 'Sci-fi';
CONCAT_WS for nullable columns. If any argument to plain
CONCAT() is NULL, the entire result is NULL. Use
CONCAT_WS(' ', first_name, middle_name, last_name) to safely build a
full name even when middle_name is NULL — CONCAT_WS skips NULL arguments but keeps
the separator between the rest.
Part 2 — Date and Time Functions
Core date and time functions
| NOW() | Current date and time as DATETIME. Stays fixed for the duration of the statement. | 2024-06-15 14:30:00 |
| CURDATE() / CURRENT_DATE | Current date only (no time). | 2024-06-15 |
| CURTIME() | Current time only (no date). | 14:30:00 |
| DATE(datetime) | Extract just the date portion from a DATETIME value. | DATE('2024-06-15 14:30:00') → '2024-06-15' |
| YEAR(date) / MONTH(date) / DAY(date) | Extract an individual component from a date. Also: HOUR, MINUTE, SECOND. | YEAR('2024-06-15') → 2024 |
| DAYOFWEEK(date) | Day number 1=Sunday … 7=Saturday. DAYNAME returns the name ('Monday' etc). | DAYOFWEEK('2024-06-15') → 7 (Saturday) |
| DATE_FORMAT(date, fmt) | Format a date/datetime as a string using format specifiers (see table below). | DATE_FORMAT(NOW(), '%d %M %Y') → '15 June 2024' |
| DATEDIFF(end, start) | Number of days between two dates (end minus start). Ignores the time component. | DATEDIFF('2024-06-15', '2024-01-01') → 166 |
| TIMESTAMPDIFF(unit, start, end) | Difference in a given unit: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR. Accounts for time. | TIMESTAMPDIFF(YEAR, '1990-03-01', CURDATE()) |
| DATE_ADD(date, INTERVAL n unit) | Add an interval to a date. Units: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND. | DATE_ADD('2024-01-01', INTERVAL 3 MONTH) → '2024-04-01' |
| DATE_SUB(date, INTERVAL n unit) | Subtract an interval from a date. Equivalent to DATE_ADD with a negative interval. | DATE_SUB(CURDATE(), INTERVAL 30 DAY) |
| STR_TO_DATE(str, fmt) | Parse a string into a DATE/DATETIME using a format pattern. The inverse of DATE_FORMAT. | STR_TO_DATE('15/06/2024', '%d/%m/%Y') |
| LAST_DAY(date) | The last day of the month for the given date. Useful for month-end reports. | LAST_DAY('2024-02-01') → '2024-02-29' |
DATE_FORMAT specifiers
| Specifier | Meaning | Example output |
|---|---|---|
| %Y | 4-digit year | 2024 |
| %y | 2-digit year | 24 |
| %m | Month number, zero-padded (01–12) | 06 |
| %M | Month name | June |
| %b | Abbreviated month name | Jun |
| %d | Day of month, zero-padded (01–31) | 15 |
| %e | Day of month, no padding (1–31) | 15 |
| %W | Weekday name | Saturday |
| %a | Abbreviated weekday name | Sat |
| %H | Hour in 24-hour format (00–23) | 14 |
| %h | Hour in 12-hour format (01–12) | 02 |
| %i | Minutes (00–59) | 30 |
| %s | Seconds (00–59) | 00 |
| %p | AM or PM | PM |
| %T | Time as HH:MM:SS (24-hour shorthand) | 14:30:00 |
Practical date queries
-- Format order dates for a customer-facing receipt
SELECT
CONCAT('ORD-', LPAD(o.order_id, 6, '0')) AS reference,
DATE_FORMAT(o.order_date, '%d %M %Y') AS order_date,
DATE_FORMAT(o.order_date, '%H:%i') AS time_placed,
CONCAT(c.first_name, ' ', c.last_name) AS customer
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date;
| reference | order_date | time_placed | customer |
|---|---|---|---|
| ORD-000001 | 14 February 2024 | 10:30 | Alice Nguyen |
| ORD-000002 | 14 February 2024 | 10:30 | Alice Nguyen |
| ORD-000003 | 05 March 2024 | 14:15 | Ben Okafor |
| ORD-000004 | 20 March 2024 | 09:00 | Chloe Martinez |
| ORD-000005 | 10 April 2024 | 16:45 | David Singh |
-- Orders placed in the last 90 days
SELECT order_id, order_date, total_price
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);
-- How many days has each customer been registered?
SELECT
CONCAT(first_name, ' ', last_name) AS customer,
joined_date,
DATEDIFF(CURDATE(), joined_date) AS days_registered
FROM customers
ORDER BY days_registered DESC;
| customer | joined_date | days_registered |
|---|---|---|
| Chloe Martinez | 2021-11-05 | 956 |
| Alice Nguyen | 2022-03-10 | 827 |
| Ben Okafor | 2023-07-22 | 328 |
| David Singh | 2024-01-18 | 149 |
-- Monthly revenue breakdown — group by year and month
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(total_price) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;
| month | orders | revenue |
|---|---|---|
| 2024-02 | 2 | 28.97 |
| 2024-03 | 2 | 30.96 |
| 2024-04 | 1 | 7.99 |
Grouping by DATE_FORMAT produces one row per calendar month — a classic pattern for revenue dashboards and trend reports.
-- Find customers who joined more than 1 year ago
-- and flag their loyalty tier
SELECT
CONCAT(first_name, ' ', last_name) AS customer,
TIMESTAMPDIFF(YEAR, joined_date, CURDATE()) AS years_member,
CASE
WHEN TIMESTAMPDIFF(YEAR, joined_date, CURDATE()) >= 3 THEN 'Gold'
WHEN TIMESTAMPDIFF(YEAR, joined_date, CURDATE()) >= 1 THEN 'Silver'
ELSE 'Bronze'
END AS tier
FROM customers
ORDER BY years_member DESC;
| customer | years_member | tier |
|---|---|---|
| Chloe Martinez | 2 | Silver |
| Alice Nguyen | 2 | Silver |
| Ben Okafor | 0 | Bronze |
| David Singh | 0 | Bronze |
CASE expressions (covered in Chapter 10) combine beautifully with date functions for business logic like loyalty tiers.
Part 3 — Numeric Functions
Core numeric functions
| ROUND(n, d) | Round n to d decimal places. d defaults to 0 (round to integer). Standard rounding: 0.5 rounds up. | ROUND(8.567, 2) → 8.57 |
| FLOOR(n) | Round down to the nearest integer, regardless of the decimal part. Negative: FLOOR(-2.3) = -3. | FLOOR(8.9) → 8 |
| CEIL(n) / CEILING(n) | Round up to the nearest integer. Negative: CEIL(-2.3) = -2. | CEIL(8.1) → 9 |
| TRUNCATE(n, d) | Remove digits after d decimal places without rounding. Different from ROUND — always truncates towards zero. | TRUNCATE(8.999, 2) → 8.99 |
| ABS(n) | Absolute value — removes the sign. ABS(-5) = ABS(5) = 5. | ABS(-42.50) → 42.50 |
| MOD(n, d) | Remainder after dividing n by d. Also written as n % d. Useful for odd/even checks and cycle detection. | MOD(10, 3) → 1 |
| POWER(n, exp) | n raised to the power of exp. Also: POW(n, exp). | POWER(2, 10) → 1024 |
| SQRT(n) | Square root of n. Returns NULL for negative values. | SQRT(25) → 5 |
| GREATEST(a, b, …) / LEAST(a, b, …) | Maximum or minimum of a list of values. Returns NULL if any argument is NULL. | GREATEST(3, 9, 2) → 9 |
| FORMAT(n, d) | Format a number with comma thousands separator and d decimal places. Returns a string — don't use for further calculations. | FORMAT(1234567.8, 2) → '1,234,567.80' |
| RAND() | Random float between 0 and 1. RAND(seed) for repeatable results. ORDER BY RAND() for random row order. | FLOOR(RAND() * 100) → 0–99 |
Practical numeric queries
-- Apply a 15% VAT to prices, rounded to 2dp
SELECT
title,
price AS price_ex_vat,
ROUND(price * 1.15, 2) AS price_inc_vat,
ROUND(price * 0.15, 2) AS vat_amount
FROM books
ORDER BY price DESC;
| title | price_ex_vat | price_inc_vat | vat_amount |
|---|---|---|---|
| Sapiens | 10.99 | 12.64 | 1.65 |
| Dune | 9.99 | 11.49 | 1.50 |
| The Midnight Library | 8.99 | 10.34 | 1.35 |
| The Left Hand of Darkness | 8.49 | 9.76 | 1.27 |
| Reasons to Stay Alive | 7.99 | 9.19 | 1.20 |
| And Then There Were None | 6.99 | 8.04 | 1.05 |
-- FLOOR vs ROUND vs TRUNCATE — see the difference clearly
SELECT
8.567 AS original,
ROUND(8.567, 2) AS rounded, -- 8.57
TRUNCATE(8.567, 2) AS truncated, -- 8.56 (no rounding)
FLOOR(8.567) AS floored, -- 8
CEIL(8.567) AS ceiled; -- 9
-- MOD use case: find books with an odd book_id
SELECT book_id, title
FROM books
WHERE MOD(book_id, 2) = 1; -- remainder 1 = odd
-- Pick 3 random books for a "You might also like" feature
SELECT title, price
FROM books
ORDER BY RAND()
LIMIT 3;
-- FORMAT for display — human-readable thousands separators
SELECT
CONCAT('£', FORMAT(SUM(total_price), 2)) AS total_revenue,
CONCAT('£', FORMAT(AVG(total_price), 2)) AS avg_order
FROM orders;
| total_revenue | avg_order |
|---|---|
| £67.92 | £13.58 |
FORMAT returns a string, not a number. Don't use FORMAT in
a calculation or WHERE clause — you'll get unexpected results or implicit type
conversion. Use FORMAT only in the SELECT list for final display output. For
rounding in calculations, use ROUND.
Chapter Summary
| Function | Key points |
|---|---|
| CONCAT / CONCAT_WS | Join strings. Use CONCAT_WS to safely handle NULL arguments — it skips them; plain CONCAT returns NULL if any argument is NULL. |
| LENGTH / CHAR_LENGTH | LENGTH counts bytes; CHAR_LENGTH counts characters. Use CHAR_LENGTH for Unicode text. |
| SUBSTRING / LEFT / RIGHT | Extract parts of a string. Position is 1-based. Omit length to read to the end. |
| REPLACE / TRIM / UPPER / LOWER | Clean and normalise text. Useful in UPDATE statements for fixing data quality issues. |
| NOW / CURDATE / CURTIME | Current datetime / date / time. NOW() stays fixed for the duration of one statement. |
| DATE_FORMAT | Format a date as a string using % specifiers. Essential for display and for grouping by month/year. |
| DATEDIFF / TIMESTAMPDIFF | DATEDIFF returns days; TIMESTAMPDIFF takes a unit (YEAR, MONTH, DAY, HOUR…) for flexible intervals. |
| DATE_ADD / DATE_SUB | Arithmetic on dates. Use INTERVAL syntax: DATE_ADD(col, INTERVAL 7 DAY). |
| ROUND / FLOOR / CEIL / TRUNCATE | ROUND rounds normally. FLOOR always rounds down, CEIL always rounds up. TRUNCATE drops digits without rounding. |
| MOD | Remainder after division. Useful for odd/even detection and cyclic patterns. |
| FORMAT | Human-readable number with thousands separators. Returns a STRING — for display only, not further calculations. |
| RAND() | Random float 0–1. Use ORDER BY RAND() LIMIT n for random sampling (slow on large tables — use with care). |