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:
| Function | Example | Result |
|---|---|---|
CHAR_LENGTH(s) / LENGTH(s) | CHAR_LENGTH('café') / LENGTH('café') | 4 / 5 (characters / bytes) |
UPPER, LOWER | UPPER('orbit') | ORBIT |
SUBSTRING(s, pos, len) | SUBSTRING('database', 1, 4) | data |
LEFT, RIGHT | RIGHT('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, RTRIM | TRIM(' hi ') | hi |
LPAD, RPAD | LPAD(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).DIVis integer division.- Division by zero returns
NULLin aSELECT. In strict mode anINSERTorUPDATEthat divides by zero fails instead (ERROR_FOR_DIVISION_BY_ZERO). ROUNDon exact decimals rounds halves away from zero, so2.675becomes2.68. OnDOUBLEvalues it may not, because2.675cannot be stored exactly; another reason to keep money inDECIMAL.
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.
| Function | Use |
|---|---|
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 unit | Date 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
| Function | Returns |
|---|---|
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 ... END | The 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
| Function | Result |
|---|---|
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
| Task | SQL |
|---|---|
| Join text, skipping NULLs | CONCAT_WS(' ', first, last) |
| Split on a delimiter | SUBSTRING_INDEX(s, '@', -1) |
| Pad numbers | LPAD(n, 6, '0') |
| Age in years | TIMESTAMPDIFF(YEAR, birth, CURDATE()) |
| Format a date | DATE_FORMAT(d, '%Y-%m-%d %H:%i') |
| Parse a date | STR_TO_DATE(s, '%d/%m/%Y') |
| First non-NULL | COALESCE(a, b, 'default') |
| Avoid division by zero | x / NULLIF(y, 0) |
| Store a value for later | SELECT ... 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?
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?
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?
Show answer
Answer: B. MySQL clamps the day to the last valid day of the target month.
Key takeaways
CONCATreturns NULL if any part is NULL;CONCAT_WSskips NULLs.- String positions start at 1;
SUBSTRING_INDEXsplits on delimiters. - Use
TIMESTAMPDIFF,DATE_ADDand 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.
@variableslast for the connection;SELECT ... INTOfills 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.