Skip to content
elephantoo

String, numeric & control-flow functions

Lesson 8 of 31 15 min read

CONCAT, SUBSTRING, REPLACE, ROUND, MOD, IFNULL, COALESCE, IF and CASE expressions.

MySQL ships with hundreds of built-in functions. You call them with NAME(arguments) anywhere an expression is allowed: in the SELECT list, in WHERE, ORDER BY, UPDATE ... SET and more. This lesson covers the string, numeric and control-flow functions you'll use every week. Dates get their own lesson next.

Sample data#

SQL
CREATE TABLE customers (
    id       INT PRIMARY KEY,
    first    VARCHAR(30),
    last     VARCHAR(30),
    email    VARCHAR(100),
    phone    VARCHAR(20),
    balance  DECIMAL(10,2)
);

INSERT INTO customers VALUES
    (1, 'ada',   'Lovelace', ' Ada@Example.COM ', '+44 20 7946 0000', 1520.456),
    (2, 'Alan',  'Turing',   'alan@example.com',   NULL,               -35.10),
    (3, 'Grace', NULL,       'grace@navy.mil',     '555-0100',         0);

Joining and splitting text#

SQL
SELECT
    CONCAT(first, ' ', last)        AS full_name,
    CONCAT_WS(' ', first, last)     AS full_name_ws,
    CHAR_LENGTH(first)              AS chars,
    UPPER(first)                    AS up,
    LOWER(last)                     AS low
FROM customers;
Output
+--------------+--------------+-------+-------+----------+
| full_name    | full_name_ws | chars | up    | low      |
+--------------+--------------+-------+-------+----------+
| ada Lovelace | ada Lovelace |     3 | ADA   | lovelace |
| Alan Turing  | Alan Turing  |     4 | ALAN  | turing   |
| NULL         | Grace        |     5 | GRACE | NULL     |
+--------------+--------------+-------+-------+----------+

Notice row 3: CONCAT returns NULL if any argument is NULL, while CONCAT_WS ("with separator") simply skips NULLs. CHAR_LENGTH counts characters. LENGTH counts bytes, which differs for non-ASCII text (LENGTH('é') is 2).

Cleaning up messy input#

SQL
SELECT
    CONCAT('[', email, ']')                  AS raw,
    LOWER(TRIM(email))                       AS clean,
    CONCAT(UPPER(LEFT(first, 1)), LOWER(SUBSTRING(first, 2))) AS proper_first
FROM customers WHERE id = 1;
Output
+---------------------+-----------------+--------------+
| raw                 | clean           | proper_first |
+---------------------+-----------------+--------------+
| [ Ada@Example.COM ] | ada@example.com | Ada          |
+---------------------+-----------------+--------------+
FunctionExampleResult
TRIM(s) / LTRIM / RTRIMTRIM(' hi ')'hi'
LEFT(s, n) / RIGHT(s, n)LEFT('MySQL', 2)'My'
SUBSTRING(s, pos, len)SUBSTRING('database', 5, 4)'base' (positions start at 1)
REPLACE(s, from, to)REPLACE('a-b-c', '-', '')'abc'
LOCATE(sub, s)LOCATE('@', 'a@b.com')2 (0 if not found)
LPAD(s, n, pad)LPAD('7', 3, '0')'007'
REVERSE(s)REVERSE('abc')'cba'
REPEAT(s, n)REPEAT('ab', 3)'ababab'

A realistic combination is splitting an email into user and domain. SUBSTRING_INDEX(s, delim, n) returns everything before the n-th delimiter (or after it, counting from the right, if n is negative):

SQL
SELECT email,
       SUBSTRING_INDEX(email, '@', 1)  AS user,
       SUBSTRING_INDEX(email, '@', -1) AS domain
FROM customers WHERE id > 1;
Output
+------------------+-------+-------------+
| email            | user  | domain      |
+------------------+-------+-------------+
| alan@example.com | alan  | example.com |
| grace@navy.mil   | grace | navy.mil    |
+------------------+-------+-------------+

Regular-expression helpers are available in MySQL 8: REGEXP_REPLACE, REGEXP_SUBSTR and REGEXP_INSTR. For example, keep only the digits of a phone number:

SQL
SELECT phone, REGEXP_REPLACE(phone, '[^0-9]', '') AS digits
FROM customers WHERE phone IS NOT NULL;
Output
+------------------+--------------+
| phone            | digits       |
+------------------+--------------+
| +44 20 7946 0000 | 442079460000 |
| 555-0100         | 5550100      |
+------------------+--------------+

Numeric functions#

SQL
SELECT
    ROUND(2.567, 1)    AS round_1,
    TRUNCATE(2.567, 1) AS trunc_1,
    CEIL(2.1)          AS ceil,
    FLOOR(-2.1)        AS floor,
    ABS(-7)            AS abs,
    MOD(17, 5)         AS mod_,
    POWER(2, 10)       AS pow,
    SQRT(144)          AS sqrt;
Output
+---------+---------+------+-------+-----+------+------+------+
| round_1 | trunc_1 | ceil | floor | abs | mod_ | pow  | sqrt |
+---------+---------+------+-------+-----+------+------+------+
|     2.6 |     2.5 |    3 |    -3 |   7 |    2 | 1024 |   12 |
+---------+---------+------+-------+-----+------+------+------+
  • ROUND(x) without a second argument rounds to a whole number. Negative precisions round to tens, hundreds and so on: ROUND(1234, -2) is 1200.
  • FLOOR rounds down (towards −∞), so FLOOR(-2.1) is −3.
  • RAND() returns a random number in [0, 1). ORDER BY RAND() LIMIT 1 picks a random row, but it's slow on big tables.
  • FORMAT(n, d) adds thousands separators for display: FORMAT(1234567.891, 2) gives '1,234,567.89'.

Integer division has its own operator. 7 / 2 is 3.5000, 7 DIV 2 is 3 and 7 % 2 is 1. Division by zero returns NULL in a SELECT (with a warning), but in strict mode an INSERT or UPDATE that divides by zero fails.

Converting types: CAST and CONVERT#

SQL
SELECT
    CAST('42' AS UNSIGNED) + 1     AS num,
    CAST(3.99 AS SIGNED)           AS to_int,
    CAST('2026-09-30' AS DATE)     AS as_date,
    CAST(42 AS CHAR)               AS as_text,
    '10' + 5                       AS implicit;
Output
+-----+--------+------------+---------+----------+
| num | to_int | as_date    | as_text | implicit |
+-----+--------+------------+---------+----------+
|  43 |      4 | 2026-09-30 | 42      |       15 |
+-----+--------+------------+---------+----------+

MySQL often converts implicitly ('10' + 5 is 15), but relying on that hides bugs. Compare numbers with numbers and strings with strings. Note that CAST(3.99 AS SIGNED) rounds to 4.

Control-flow functions: IF, CASE, IFNULL, COALESCE, NULLIF#

These let you make decisions inside a query.

SQL
SELECT
    first,
    balance,
    IF(balance < 0, 'overdrawn', 'ok')      AS status,
    CASE
        WHEN balance >= 1000 THEN 'gold'
        WHEN balance > 0     THEN 'standard'
        ELSE 'none'
    END                                      AS tier,
    IFNULL(phone, 'no phone')                AS phone_display,
    COALESCE(last, first, 'unknown')         AS sort_name
FROM customers;
Output
+-------+---------+-----------+------+------------------+-----------+
| first | balance | status    | tier | phone_display    | sort_name |
+-------+---------+-----------+------+------------------+-----------+
| ada   | 1520.46 | ok        | gold | +44 20 7946 0000 | Lovelace  |
| Alan  |  -35.10 | overdrawn | none | no phone         | Turing    |
| Grace |    0.00 | ok        | none | 555-0100         | Grace     |
+-------+---------+-----------+------+------------------+-----------+
  • IF(cond, a, b) returns a when the condition is true and b otherwise.
  • CASE WHEN ... THEN ... ELSE ... END checks conditions in order and returns the first match. Without an ELSE, the result is NULL. There's also the "simple" form: CASE status WHEN 'A' THEN 'Active' WHEN 'B' THEN 'Blocked' END.
  • IFNULL(x, fallback) replaces a NULL. COALESCE(a, b, c, ...) is the standard-SQL version and takes any number of arguments.
  • NULLIF(a, b) returns NULL when a = b. The classic use is avoiding division by zero: total / NULLIF(count, 0).

CASE also works in ORDER BY and inside aggregates. That's a powerful trick you'll use in the aggregates lesson:

SQL
SELECT SUM(CASE WHEN balance > 0 THEN 1 ELSE 0 END) AS positive_accounts FROM customers;
Output
+-------------------+
| positive_accounts |
+-------------------+
|                 1 |
+-------------------+

Using functions in UPDATE#

Functions are great for one-off data clean-ups:

SQL
UPDATE customers
SET email = LOWER(TRIM(email)),
    first = CONCAT(UPPER(LEFT(first, 1)), SUBSTRING(first, 2))
WHERE id = 1;

SELECT first, email FROM customers WHERE id = 1;
Output
+-------+-----------------+
| first | email           |
+-------+-----------------+
| Ada   | ada@example.com |
+-------+-----------------+

Common mistakes#

  • Forgetting that positions start at 1. SUBSTRING(s, 0, 3) returns an empty string.
  • Wrapping indexed columns in functions in WHERE. WHERE LOWER(email) = 'x' can't use a normal index on email. Clean data on the way in instead (or use a functional index, covered in Indexes).
  • CONCAT with nullable columns. Use CONCAT_WS or COALESCE.
  • Formatting numbers in SQL for the database's own use. FORMAT() returns a string, so sorting on it sorts text. Format only for display, and ideally in the application.

What's next#

Dates and times deserve a lesson of their own: formatting, adding intervals, computing ages and differences, and dealing with time zones.

Check your understanding

Quick quiz

0/3 answered

  1. 1.What does CONCAT('Hello', NULL, 'World') return in MySQL?

  2. 2.Which expression returns the first non-NULL value among its arguments?

  3. 3.What is ROUND(2.567, 1) versus TRUNCATE(2.567, 1)?

Finished reading?

Mark this lesson complete to track your progress.