MySQL Built-in Functions

MySQL has hundreds of built-in functions.

Concept

MySQL has hundreds of built-in functions. This module covers the most used string, numeric, date/time, control-flow, NULL, conversion, and information functions — with MySQL-specific names and behaviors.

Why It Matters

Functions clean data, compute derived values, format output, and implement business rules directly in queries.

Syntax

SELECT FUNCTION_NAME(args) FROM ...;

Example

10.1 String functions CONCAT(a, b,...) CONCAT('ECU', '-', 7) ECU-7 (NULL if any argument is NULL) CONCAT_WS(sep, a, b,...)

CONCAT_WS(', ', 'Pune', NULL, 'India') Pune, India (skips NULLs) 2 (bytes in utf8mb4) 1 (characters) UPPER, LOWER TRIM, LTRIM, RTRIM TRIM(LEADING '0' FROM s) TRIM(LEADING '0' FROM '000123') SUBSTRING(s, pos, len) SUBSTRING('EHBAUREV250000001', 10, 2) LEFT, RIGHT LEFT('P0301', 1) SUBSTRING_INDEX(s, delim, n)

SUBSTRING_INDEX('[email protected]', '@', -1) LOCATE(sub, s) / INSTR(s, sub) LOCATE('@', '[email protected]') REPLACE(s, from, to) REPLACE('1.1.0', '.', '_') LPAD, RPAD LPAD(7, 3, '0') REPEAT, REVERSE, SPACE REPEAT('-', 5) FORMAT(n, d) FORMAT(1234567.891, 2) 1,234,567.89 (string!) HEX, UNHEX, CONV CONV('1A0', 16, 10) FIND_IN_SET(s, list) FIND_IN_SET('LIN', 'CAN,LIN') GROUP_CONCAT (aggregate) 10.2 Numeric functions

ROUND(1234.567, 2) / ROUND(1234.567, -2) TRUNCATE(x, d) TRUNCATE(4.99, 1) CEIL(x) / CEILING(x) MOD(a, b) / a % b MOD(200, 12) POWER(x, y) / POW POWER(2, 10) SQRT, EXP, LN, LOG10, LOG2 random 0 ≤ x < 1 GREATEST, LEAST GREATEST(3, 9, 5) DIV (operator)

ROUND on exact values (DECIMAL) rounds half away from zero; on approximate values (FLOAT/DOUBLE) it follows the C library (usually round-half-to-even).

Physical value of a CAN signal (raw × factor + offset):

10.3 Date and time functions NOW() / CURRENT_TIMESTAMP 2026-09-29 10:15:02 (statement start time) Time at the moment of execution CURDATE() / CURRENT_DATE CURTIME() / CURRENT_TIME Current UTC time DATE(dt), TIME(dt) DATE('2026-03-10 18:30')

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND DAYNAME, MONTHNAME, DAYOFWEEK (1=Sunday), WEEKDAY (0=Monday) WEEK(d, 3) / YEARWEEK(d, 3) ISO week number EXTRACT(unit FROM d) EXTRACT(YEAR_MONTH FROM '2026-03-10')

DATE_ADD(d, INTERVAL n unit) / d + INTERVAL n unit '2026-01-31' + INTERVAL 1 MONTH DATE_SUB(d, INTERVAL n unit)

DATE_SUB('2026-03-10', INTERVAL 7 DAY) DATEDIFF(d2, d1) DATEDIFF('2026-03-10', '2026-01-05') 64 (days, d2 − d1) TIMESTAMPDIFF(unit, t1, t2)

TIMESTAMPDIFF(MINUTE, '2026-02-05 14:20', '2026-02-06 09:00') TIMEDIFF(t2, t1) TIMEDIFF('10:30:00', '09:00:00') DATE_FORMAT(d, fmt) DATE_FORMAT('2026-03-10', '%d %b %Y') STR_TO_DATE(s, fmt) STR_TO_DATE('10/03/2026', '%d/%m/%Y')

UNIX_TIMESTAMP(dt) / FROM_UNIXTIME(n) FROM_UNIXTIME(0) (session TZ UTC) CONVERT_TZ(dt, from, to)

CONVERT_TZ('2026-03-10 18:30', '+00:00', '+05:30') Common format specifiers: %Y year, %m month 01–12, %d day, %H hour 00–23, %i minutes, %s seconds, %f microseconds, %b short month name, %W weekday name.

Named time zones ('Asia/Kolkata') in CONVERT_TZ need the time-zone tables loaded (mysql_tzinfo_to_sql); numeric offsets always work.

Years of service as of 2026-09-29:

10.4 Control-flow and NULL functions IF(cond, a, b) a if cond is TRUE, else b IF(stock = 0, 'OUT', 'IN') IFNULL(a, b) a unless NULL, then b IFNULL(email, 'n/a') COALESCE(a, b,...) First non-NULL (standard) COALESCE(mobile, work_phone, 'none') NULLIF(a, b) price / NULLIF(stock, 0) CASE... END Standard conditional expression

1 if a is NULL, else 0 (one argument in MySQL) 10.5 Conversion functions

MySQL CAST targets: BINARY, CHAR[(n)], DATE, DATETIME, TIME, DECIMAL(p,s), SIGNED, UNSIGNED, JSON, FLOAT/DOUBLE (8.0.17+), YEAR (8.0.22+). There is no CAST(x AS INT); use SIGNED.

Implicit conversion warning: MySQL converts strings to numbers leniently: SELECT '12abc' + 1; returns 13 with a warning, and WHERE varchar_col = 123 compares numerically, which prevents index use and can match '123abc'. Always compare strings with strings.

10.6 Information functions

SELECT CONCAT(UPPER(last_name), ', ', first_name)        AS display_name,
       SUBSTRING_INDEX(email, '@', 1)                     AS mailbox,
       CHAR_LENGTH(first_name)                            AS name_len,
       CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))    AS initials
FROM employees
WHERE dept_id = 1
ORDER BY emp_id;

+----------------+--------------+----------+----------+
| display_name   | mailbox      | name_len | initials |
+----------------+--------------+----------+----------+
| RAO, Asha      | asha.rao     |        4 | AR       |
| SHAH, Vikram   | vikram.shah  |        6 | VS       |
| IYER, Neha     | neha.iyer    |        4 | NI       |
| PILLAI, Meera  | meera.pillai |        5 | MP       |
+----------------+--------------+----------+----------+

SELECT ROUND(CONV('0FA0', 16, 10) * 0.1 + 0, 1) AS pack_voltage_v;   -- 4000 * 0.1 = 400.0

SELECT first_name, hire_date,
       TIMESTAMPDIFF(YEAR, hire_date, '2026-09-29') AS years_of_service
FROM employees
ORDER BY hire_date;

+------------+------------+------------------+
| first_name | hire_date  | years_of_service |
+------------+------------+------------------+
| Asha       | 2015-03-01 |               11 |
| Rahul      | 2017-09-01 |                9 |
| Vikram     | 2018-06-15 |                8 |
| Arjun      | 2019-11-05 |                6 |
| Neha       | 2021-01-10 |                5 |
| Priya      | 2022-04-20 |                4 |
| Meera      | 2022-07-18 |                4 |
| Kiran      | 2023-02-01 |                3 |
+------------+------------+------------------+

SELECT first_name, salary,
       CASE
           WHEN salary >= 150000 THEN 'Band A'
           WHEN salary >= 90000  THEN 'Band B'
           ELSE 'Band C'
       END AS band,
       IF(dept_id IS NULL, 'Unassigned', 'Assigned') AS dept_status
FROM employees
ORDER BY emp_id;

+------------+-----------+--------+-------------+
| first_name | salary    | band   | dept_status |
+------------+-----------+--------+-------------+
| Asha       | 250000.00 | Band A | Assigned    |
| Vikram     | 150000.00 | Band A | Assigned    |
| Neha       |  95000.00 | Band B | Assigned    |
| Rahul      | 120000.00 | Band B | Assigned    |
| Priya      |  60000.00 | Band C | Assigned    |
| Arjun      |  90000.00 | Band B | Assigned    |
| Kiran      |  80000.00 | Band C | Unassigned  |
| Meera      |  95000.00 | Band B | Assigned    |
+------------+-----------+--------+-------------+

SELECT CAST('2026-09-29' AS DATE),
       CAST(price AS UNSIGNED)          FROM products LIMIT 1;    -- integer cast (rounds DECIMAL in MySQL)
SELECT CAST('42' AS SIGNED) + 1;                                   -- 43
SELECT CONVERT('abc' USING utf8mb4);                               -- charset conversion
SELECT CAST(12.5 AS DECIMAL(10,2)), CAST(3.7 AS CHAR);

SELECT DATABASE(), USER(), CURRENT_USER(), VERSION(), CONNECTION_ID(), LAST_INSERT_ID(), ROW_COUNT(), FOUND_ROWS();
SELECT UUID(), UUID_SHORT();
SELECT SLEEP(1);        -- pause (testing only)
SELECT BENCHMARK(1000000, SHA2('x', 256));   -- measure expression speed

Expected Result

SELECT order_id,
       DATE_FORMAT(order_date, '%b %d, %Y')                            AS order_day,
       DATEDIFF('2026-03-31', order_date)                              AS age_days,
       IF(status = 'CANCELLED', 0, total_amount)                      AS billable
FROM orders
ORDER BY order_id;

+----------+--------------+----------+----------+
| order_id | order_day    | age_days | billable |
+----------+--------------+----------+----------+
|     1001 | Jan 05, 2026 |       85 | 75800.00 |
|     1002 | Jan 18, 2026 |       72 | 12000.00 |
|     1003 | Feb 02, 2026 |       57 | 18100.00 |
|     1004 | Feb 14, 2026 |       45 |     0.00 |
|     1005 | Mar 01, 2026 |       30 |  1600.00 |
|     1006 | Mar 10, 2026 |       21 | 93000.00 |
+----------+--------------+----------+----------+

Common Mistakes

LENGTH instead of CHAR_LENGTH for multibyte text.

CONCAT returning NULL because one argument is NULL (use CONCAT_WS or IFNULL).

ISNULL(a, b) (SQL Server habit) — MySQL's ISNULL takes one argument.

FORMAT() output used in calculations (it is a string with commas).

DATEDIFF argument order (end, start) and TIMESTAMPDIFF order (unit, start, end).

Functions on indexed columns in WHERE (WHERE YEAR(order_date) = 2026) — non-sargable.

Comparing VARCHAR columns to numbers.

Best Practices

Prefer standard functions (COALESCE, CASE, CAST) for portability; use MySQL-specific ones deliberately.

Keep indexed columns bare in WHERE; move arithmetic to constants.

Format dates and numbers at the presentation layer when possible.

Store times in UTC; convert with CONVERT_TZ for display.

Interview Relevance

High: "IFNULL vs COALESCE?", "LENGTH vs CHAR_LENGTH?", "DATEDIFF vs TIMESTAMPDIFF?", "NOW() vs SYSDATE()?", "How do you extract the domain from an email in MySQL?".

Practice Questions

Show each customer's email domain, or 'MISSING' if NULL.

Show products with price rounded up to the nearest thousand.

Show the month name and ISO week of each order.

Convert the CAN ID 0x2F0 to decimal and back to hex.

Classify products as Budget (< 1000), Mid (1000–20000), or Premium (> 20000).