Lesson 11 intermediate

Built-in Functions and Variables

A practical tour of MySQL's built-in functions: strings (CONCAT, CONCAT_WS, SUBSTRING_INDEX, LPAD, REPLACE, TRIM), numbers (ROUND, TRUNCATE, DIV, MOD), dates and times (DATE_ADD, DATEDIFF, TIMESTAMPDIFF, DATE_FORMAT, STR_TO_DATE, LAST_DAY, CONVERT_TZ), control flow (IF, IFNULL, COALESCE, NULLIF, GREATEST), CAST and the implicit conversion traps, plus user-defined variables and SELECT ... INTO.

MySQL ships with hundreds of functions. You do not need to memorise them, but knowing the common ones by heart saves round trips to the application code. This lesson groups them by job and points out the behaviours that surprise people.

Strings

SELECT CONCAT('Orbit', ' ', 'notes')        AS joined,
       CONCAT_WS('-', '2026', '11', '11')   AS with_sep,
       CONCAT('x', NULL)                    AS with_null;
+-------------+------------+-----------+
| joined      | with_sep   | with_null |
+-------------+------------+-----------+
| Orbit notes | 2026-11-11 | NULL      |
+-------------+------------+-----------+
1 row in set (0.00 sec)

CONCAT returns NULL if any argument is NULL, a frequent source of empty labels. CONCAT_WS (with separator) skips NULL arguments, which makes it the safer choice for building names and addresses.

SUBSTRING_INDEX(str, delim, n) returns everything before the n-th delimiter, or after it when n is negative. It splits emails in one call:

SELECT email,
       SUBSTRING_INDEX(email, '@', 1)  AS local_part,
       SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users ORDER BY id;
+----------------+------------+-----------+
| email          | local_part | domain    |
+----------------+------------+-----------+
| ana@orbit.dev  | ana        | orbit.dev |
| ben@orbit.dev  | ben        | orbit.dev |
| chen@orbit.dev | chen       | orbit.dev |
+----------------+------------+-----------+
3 rows in set (0.00 sec)

More string helpers:

FunctionExampleResult
CHAR_LENGTH(s) / LENGTH(s)CHAR_LENGTH('café') / LENGTH('café')4 / 5 (characters / bytes)
UPPER, LOWERUPPER('orbit')ORBIT
SUBSTRING(s, pos, len)SUBSTRING('database', 1, 4)data
LEFT, RIGHTRIGHT('notes.txt', 3)txt
LOCATE(sub, s)LOCATE('@', 'ana@orbit.dev')4
REPLACE(s, from, to)REPLACE('a-b-c', '-', '/')a/b/c
TRIM, LTRIM, RTRIMTRIM(' hi ')hi
LPAD, RPADLPAD(7, 4, '0')0007
REPEAT(s, n)REPEAT('ab', 3)ababab
REVERSE(s)REVERSE('abc')cba
FORMAT(n, d)FORMAT(1234567.891, 2)1,234,567.89

Positions in MySQL strings start at 1, not 0.

Numbers

SELECT ROUND(2.675, 2)    AS rounded,
       TRUNCATE(2.675, 2) AS truncated,
       CEIL(2.1)          AS ceil_,
       FLOOR(-2.1)        AS floor_,
       7 / 2              AS divided,
       7 DIV 2            AS int_div,
       7 % 2              AS remainder,
       1 / 0              AS by_zero;
+---------+-----------+-------+--------+---------+---------+-----------+---------+
| rounded | truncated | ceil_ | floor_ | divided | int_div | remainder | by_zero |
+---------+-----------+-------+--------+---------+---------+-----------+---------+
|    2.68 |      2.67 |     3 |     -3 |  3.5000 |       3 |         1 |    NULL |
+---------+-----------+-------+--------+---------+---------+-----------+---------+
1 row in set (0.00 sec)
  • / always returns a decimal, with four extra digits by default (div_precision_increment). DIV is integer division.
  • Division by zero returns NULL in a SELECT. In strict mode an INSERT or UPDATE that divides by zero fails instead (ERROR_FOR_DIVISION_BY_ZERO).
  • ROUND on exact decimals rounds halves away from zero, so 2.675 becomes 2.68. On DOUBLE values it may not, because 2.675 cannot be stored exactly; another reason to keep money in DECIMAL.

Also handy: ABS, POW, SQRT, MOD(a, b), GREATEST(a, b, ...) and LEAST(a, b, ...).

Dates and times

SELECT DATE_ADD('2026-11-11', INTERVAL 1 MONTH)     AS next_month,
       '2026-01-31' + INTERVAL 1 MONTH              AS end_of_jan,
       DATEDIFF('2026-12-25', '2026-11-11')         AS days_left,
       LAST_DAY('2026-02-10')                       AS last_day,
       DAYNAME('2026-11-11')                        AS weekday;
+------------+------------+-----------+------------+-----------+
| next_month | end_of_jan | days_left | last_day   | weekday   |
+------------+------------+-----------+------------+-----------+
| 2026-12-11 | 2026-02-28 |        44 | 2026-02-28 | Wednesday |
+------------+------------+-----------+------------+-----------+
1 row in set (0.00 sec)

Adding a month to 31 January gives the last valid day of February rather than an error or 3 March.

FunctionUse
NOW(), CURDATE(), CURTIME()Current date and time (constant within a statement)
UTC_TIMESTAMP()Current time in UTC, whatever the session time zone
DATE(dt), TIME(dt), YEAR(dt), MONTH(dt), DAY(dt), HOUR(dt)Extract parts
DATE_ADD(d, INTERVAL n unit), d - INTERVAL n unitDate arithmetic (units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR)
DATEDIFF(a, b)Days between two dates, ignoring times
TIMESTAMPDIFF(unit, a, b)Whole units between two values, for example age in years
DATE_FORMAT(d, fmt)Date to text
STR_TO_DATE(s, fmt)Text to date
CONVERT_TZ(dt, from, to)Change time zone
UNIX_TIMESTAMP(dt), FROM_UNIXTIME(n)Epoch seconds in and out
SELECT TIMESTAMPDIFF(YEAR, '1990-05-20', '2026-11-11')                 AS age,
       TIMESTAMPDIFF(HOUR, '2026-11-11 08:00:00', '2026-11-12 10:30:00') AS hours,
       DATE_FORMAT('2026-11-11 14:05:00', '%d/%m/%Y %H:%i')             AS formatted,
       STR_TO_DATE('11/11/2026', '%d/%m/%Y')                           AS parsed;
+-----+-------+------------------+------------+
| age | hours | formatted        | parsed     |
+-----+-------+------------------+------------+
|  36 |    26 | 11/11/2026 14:05 | 2026-11-11 |
+-----+-------+------------------+------------+
1 row in set (0.00 sec)

The most used DATE_FORMAT codes: %Y year, %m month 01 to 12, %d day, %H hour 00 to 23, %i minutes, %s seconds, %a/%W short/full weekday, %b/%M short/full month name.

Note: CONVERT_TZ with named zones such as 'America/Sao_Paulo' returns NULL until the time zone tables are loaded. On Linux run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql once. Offsets like '-03:00' work without them.

Control flow

FunctionReturns
IF(cond, a, b)a when cond is true, else b
IFNULL(x, fallback)x, or fallback when x is NULL
COALESCE(a, b, c, ...)The first non-NULL argument
NULLIF(a, b)NULL when a = b, else a
CASE WHEN ... THEN ... ELSE ... ENDThe general form (lesson 7)

NULLIF is great for avoiding division by zero: total / NULLIF(count, 0) returns NULL instead of failing when count is 0.

Converting types

CAST(expr AS type) and CONVERT(expr, type) convert explicitly. The target types are SIGNED, UNSIGNED, DECIMAL(p, s), CHAR, DATE, DATETIME, TIME, JSON, BINARY, and since 8.0.17 FLOAT and DOUBLE:

SELECT CAST('42' AS UNSIGNED) + 1, CAST(3.99 AS SIGNED), CAST('2026-11-11' AS DATE);

MySQL also converts implicitly when you mix types, and that is where bugs hide:

SELECT '5 apples' + 1 AS sum_, 'abc' = 0 AS equals_zero;
+------+-------------+
| sum_ | equals_zero |
+------+-------------+
|    6 |           1 |
+------+-------------+
1 row in set (0.00 sec)

Both produce warning 1292 (“Truncated incorrect DOUBLE value”). Comparing a string with a number converts the string to a number, and any text that does not start with digits becomes 0. So WHERE email = 0 matches every row whose email starts with a letter, and WHERE varchar_code = 123 cannot use the index on varchar_code. Always compare strings with strings: WHERE code = '123'.

User-defined variables

Variables that start with @ live for the duration of your connection:

SET @uid = (SELECT id FROM users WHERE email = 'ben@orbit.dev');
SELECT title FROM notes WHERE user_id = @uid ORDER BY id;
+------------+
| title      |
+------------+
| Book ideas |
| MySQL tips |
+------------+
2 rows in set (0.00 sec)

SELECT ... INTO stores query results in variables; the query must return exactly one row:

SELECT COUNT(*), MAX(created_at) INTO @total, @latest FROM notes;
SELECT @total, @latest;
+--------+---------------------+
| @total | @latest             |
+--------+---------------------+
|      5 | 2026-11-02 10:00:00 |
+--------+---------------------+
1 row in set (0.00 sec)

Variables are handy for scripts and the mysql prompt. Inside stored procedures you use DECLAREd local variables instead (lesson 15). Do not assign variables inside expressions of a SELECT (@n := @n + 1): that trick was used for row numbering before window functions, is deprecated, and its evaluation order is not guaranteed.

Hashing and identifiers

FunctionResult
UUID()A new UUID string (lesson 5 shows compact storage)
MD5(s), SHA2(s, 256)Hex digests, for checksums and deduplication
RANDOM_BYTES(n)Cryptographically strong random bytes
CRC32(s)Fast 32-bit checksum

None of these is a password hash. Applications should hash passwords with bcrypt, scrypt or Argon2 in application code.

Quick reference

TaskSQL
Join text, skipping NULLsCONCAT_WS(' ', first, last)
Split on a delimiterSUBSTRING_INDEX(s, '@', -1)
Pad numbersLPAD(n, 6, '0')
Age in yearsTIMESTAMPDIFF(YEAR, birth, CURDATE())
Format a dateDATE_FORMAT(d, '%Y-%m-%d %H:%i')
Parse a dateSTR_TO_DATE(s, '%d/%m/%Y')
First non-NULLCOALESCE(a, b, 'default')
Avoid division by zerox / NULLIF(y, 0)
Store a value for laterSELECT ... INTO @v

Exercise

A users table has first_name and a nullable middle_name. Which expression builds a full name without returning NULL when middle_name is missing?

  1. CONCAT_WS(‘ ‘, first_name, middle_name, last_name)
  2. CONCAT(first_name, ‘ ‘, middle_name, ‘ ‘, last_name)
  3. first_name + ‘ ‘ + middle_name + ‘ ‘ + last_name
  4. CONCAT(first_name, middle_name, last_name) IS NOT NULL
Show answer

Answer: A. CONCAT returns NULL if any argument is NULL, and + is numeric addition in MySQL. CONCAT_WS skips NULL arguments.

Exercise

A VARCHAR column code contains values like ‘A12’ and ‘123’. Why is WHERE code = 0 dangerous?

  1. It raises an error in strict mode
  2. It only matches the string ‘0’
  3. It matches nothing, because 0 is not a string
  4. Strings are converted to numbers, so every code that does not start with a digit equals 0
Show answer

Answer: D. Comparing a string to a number converts the string to a number; ‘A12’ becomes 0. Compare strings with string literals, which also lets the index be used.

Exercise

What does SELECT ‘2026-01-31’ + INTERVAL 1 MONTH return?

  1. 2026-03-03
  2. 2026-02-28
  3. NULL with a warning
  4. 2026-02-31
Show answer

Answer: B. MySQL clamps the day to the last valid day of the target month.

Key takeaways

  • CONCAT returns NULL if any part is NULL; CONCAT_WS skips NULLs.
  • String positions start at 1; SUBSTRING_INDEX splits on delimiters.
  • Use TIMESTAMPDIFF, DATE_ADD and half-open ranges for dates, and load time zone tables before using named zones.
  • Implicit string-to-number conversion silently turns text into 0: compare like with like.
  • @variables last for the connection; SELECT ... INTO fills them from a single-row query.

FAQ

Is NOW() different from SYSDATE()?

NOW() returns the time the statement started and stays constant during it, which is safe for replication. SYSDATE() returns the time at the moment it is evaluated, so it can change within one statement. Use NOW().

How do I get the week number?

WEEK(date, 3) returns the ISO-8601 week (weeks start on Monday, week 1 contains the first Thursday). YEARWEEK(date, 3) adds the year, which avoids mixing week 1 of different years.

How do I strip HTML tags or other patterns?

Use REGEXP_REPLACE(s, '<[^>]+>', ''). For anything complicated, clean the data in the application before storing it.

Why does my date function return NULL?

Usually because the input is not a valid date (for example STR_TO_DATE with a format that does not match the text) or a named time zone is not loaded. Run SHOW WARNINGS; right after the query to see why.

↑ ↓ navigate Enter open Esc close