Topic 34 of 52 · Intermediate
Functions Reference: String & Numeric
Every common MySQL string and math function, what it does and an example.
Concept
A function takes input values and returns one value per row. String functions clean and reshape text; numeric functions round and calculate.
Functions can be used in SELECT, WHERE, ORDER BY, GROUP BY and UPDATE. Wrapping an indexed column in a function inside WHERE usually stops the index being used.
Positions in MySQL strings start at 1, not 0.
Cheat sheet
| Function | What it does | Example → result |
|---|---|---|
| CONCAT(a, b, ...) | Joins strings (NULL if any arg is NULL) | CONCAT('Ab','cd') → 'Abcd' |
| CONCAT_WS(sep, a, b) | Joins with a separator, skips NULLs | CONCAT_WS('-','a','b') → 'a-b' |
| LENGTH(s) | Length in bytes | LENGTH('abc') → 3 |
| CHAR_LENGTH(s) | Length in characters | CHAR_LENGTH('héllo') → 5 |
| UPPER(s) / LOWER(s) | Change case | UPPER('sql') → 'SQL' |
| TRIM(s) / LTRIM / RTRIM | Remove spaces (or chars) | TRIM(' hi ') → 'hi' |
| SUBSTRING(s, pos, len) | Part of a string | SUBSTRING('Hyderabad',1,3) → 'Hyd' |
| LEFT(s, n) / RIGHT(s, n) | First / last n characters | RIGHT('2026-10',2) → '10' |
| SUBSTRING_INDEX(s, d, n) | Text before the n-th delimiter | SUBSTRING_INDEX('a@b.com','@',1) → 'a' |
| INSTR(s, sub) / LOCATE(sub, s) | Position of a substring (0 if absent) | INSTR('mysql','sql') → 3 |
| REPLACE(s, from, to) | Replace all occurrences | REPLACE('a-b-c','-','') → 'abc' |
| REVERSE(s) | Reverse a string | REVERSE('abc') → 'cba' |
| LPAD(s, n, p) / RPAD | Pad to length n | LPAD('7',3,'0') → '007' |
| REPEAT(s, n) | Repeat a string | REPEAT('ab',2) → 'abab' |
| FORMAT(x, d) | Number with commas and d decimals | FORMAT(1234567.8,1) → '1,234,567.8' |
| REGEXP_LIKE / REGEXP_REPLACE | Regex test / replace (MySQL 8) | REGEXP_REPLACE('a1b2','[0-9]','') → 'ab' |
| ROUND(x, d) | Round to d decimals | ROUND(3.456,2) → 3.46 |
| TRUNCATE(x, d) | Cut decimals without rounding | TRUNCATE(3.456,1) → 3.4 |
| CEIL(x) / FLOOR(x) | Round up / down to integer | CEIL(4.1) → 5 |
| ABS(x) | Absolute value | ABS(-5) → 5 |
| MOD(a, b) / a % b | Remainder | MOD(10,3) → 1 |
| POWER(x, y) / SQRT(x) | Power / square root | POWER(2,10) → 1024 |
| RAND() | Random number 0–1 | ORDER BY RAND() LIMIT 1 |
| SIGN(x) | -1, 0 or 1 | SIGN(-8) → -1 |
| GREATEST / LEAST | Largest / smallest of a list | GREATEST(3,9,4) → 9 |
| CAST(x AS type) | Change data type (standard SQL) | CAST('42' AS UNSIGNED) → 42 |
| CONVERT(x, type) / CONVERT(s USING cs) | Change type or character set | CONVERT('3.7', DECIMAL(4,1)) → 3.7 |
| COALESCE(a, b, ...) | First non-NULL value | COALESCE(NULL, 0) → 0 |
| CRC32(s) | 32-bit checksum of a string | CRC32('MySQL') → 3259397556 |
| REGEXP_SUBSTR(s, pattern) | Text matching a regex (MySQL 8) | REGEXP_SUBSTR('order#4521','[0-9]+') → '4521' |
| REGEXP_INSTR(s, pattern) | Position of a regex match | REGEXP_INSTR('ab12','[0-9]') → 3 |
Code (SQL · MySQL 8)
SELECT
CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS proper_name,
SUBSTRING_INDEX(email, '@', -1) AS email_domain,
LPAD(id, 6, '0') AS customer_code,
REPLACE(phone, ' ', '') AS clean_phone
FROM customers;
SELECT order_id,
ROUND(amount * 1.18, 2) AS with_gst,
CEIL(amount / 500) AS slabs,
MOD(order_id, 2) = 0 AS is_even_id
FROM orders;Remember
- String positions start at 1.
- CONCAT returns NULL if any input is NULL — use CONCAT_WS or COALESCE.
- ROUND rounds, TRUNCATE just cuts.
Interview questions
How do you extract the domain from an email?
SUBSTRING_INDEX(email, '@', -1) returns everything after the last '@'.
LENGTH vs CHAR_LENGTH?
LENGTH counts bytes; CHAR_LENGTH counts characters. They differ for multi-byte (UTF-8) text.
Lesson test
Take an 8-question test on “Functions Reference: String & Numeric” — concepts, output prediction, bug spotting and interview questions.