Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 75 ยท Querying with MySQL

Strings, Dates and Math Functions

A practical tour of MySQL functions: CONCAT_WS, SUBSTRING_INDEX, REGEXP_REPLACE, DATE_FORMAT, DATE_ADD, TIMESTAMPDIFF, ROUND vs TRUNCATE and SQLite equivalents.

Real queries rarely return columns exactly as stored. You glue first and last names together, cut a domain out of an email address, format a date for a report, find how many days passed between two events, or round a price. MySQL ships with hundreds of built-in functions for these jobs. This lesson covers the ones you will reach for every week, and flags the places where MySQL behaves differently from other databases.

A note on what runs here: the in-browser engine is SQLite, which has its own, smaller set of functions with different names. MySQL function calls are shown as read-only code with the results MySQL gives, and every topic also has a live SQLite version that does the same job, so you can see the idea working.

Three families

Strings Join, cut, search, replace.
Dates Format, add, subtract, compare.
Math Round, truncate, mod, power.

Functions run per row, so on a big table, where you use them matters.

1 / 2

String functions

The functions below are the everyday ones. Results are what MySQL returns:

SQL
SELECT CONCAT('Ada', ' ', 'Lovelace');               -- 'Ada Lovelace'
SELECT CONCAT('Ada', NULL, 'Lovelace');              -- NULL: any NULL poisons CONCAT
SELECT CONCAT_WS('-', '2026', NULL, '03', '07');     -- '2026-03-07': NULLs are skipped
SELECT SUBSTRING('database', 1, 4);                  -- 'data' (positions start at 1)
SELECT LEFT('database', 4), RIGHT('database', 4);    -- 'data', 'base'
SELECT SUBSTRING_INDEX('www.example.com', '.', 2);   -- 'www.example'
SELECT SUBSTRING_INDEX('www.example.com', '.', -1);  -- 'com'
SELECT LPAD('7', 3, '0');                            -- '007'
SELECT REPLACE('a-b-c', '-', '+');                   -- 'a+b+c'
SELECT TRIM('  padded  ');                           -- 'padded'
SELECT UPPER('mysql'), LOWER('MySQL');               -- 'MYSQL', 'mysql'
SELECT LOCATE('@', '[email protected]');               -- 4 (0 when not found)
SELECT CHAR_LENGTH('รฉ'), LENGTH('รฉ');                -- 1, 2 (characters vs bytes in utf8mb4)

Points worth remembering:

  • CONCAT returns NULL if any argument is NULL. CONCAT_WS(separator, ...) skips NULL arguments instead, which makes it the right choice for building an address line from optional parts. (The separator itself being NULL gives NULL.)
  • || is not concatenation in MySQL. By default it is the logical OR operator. Only with the PIPES_AS_CONCAT SQL mode does it concatenate. Always write CONCAT(...) (SQL Modes and Strictness).
  • SUBSTRING_INDEX(str, delimiter, count) returns everything before the count-th delimiter, or, with a negative count, everything after the count-th delimiter from the right. It is perfect for splitting emails, paths and dotted names without a regular expression.
  • LENGTH counts bytes, CHAR_LENGTH counts characters. For plain English text they agree. For accented letters, emoji or Chinese they do not, and validating user input with LENGTH rejects valid names (Character Sets and Collations).
  • Comparison and LIKE obey the column's collation, so 'MySQL' = 'mysql' is true under the usual case-insensitive collations.

SQLite has substr, instr (like LOCATE with swapped arguments), replace, trim, upper, lower, length (characters for text) and printf. The || operator does concatenation there. This example does the same jobs, including a hand-made SUBSTRING_INDEX:

SQL String functions, SQLite style

Press Run (or Ctrl + Enter) to see the result here.

The breaks_on_null column is NULL for Ada and Alan, the same trap as CONCAT in MySQL (here || behaves like it). The safe_name column fixes it by treating the middle name as optional, which is what CONCAT_WS(' ', first, middle, last) does in one call. The last query shows one character that occupies two bytes, the reason LENGTH and CHAR_LENGTH differ in MySQL.

Regular expressions

MySQL 8.0 has REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR and REGEXP_INSTR (the older REGEXP operator still works). They use the ICU library, so the syntax is Perl-like:

SQL
SELECT REGEXP_REPLACE('Order #A-1042 / #B-77', '[0-9]+', 'N');   -- 'Order #A-N / #B-N'
SELECT REGEXP_SUBSTR('call 555-0142 now', '[0-9]{3}-[0-9]{4}');   -- '555-0142'
SELECT '[email protected]' REGEXP '^[a-z]+@[a-z.]+$';               -- 1
SELECT REGEXP_REPLACE('Ada  Lovelace', ' +', ' ');                 -- 'Ada Lovelace'

Regular expressions cannot use an ordinary index, so use them for cleaning and one-off searches rather than hot lookups. SQLite has no regex function built in, so these remain read-only here.

The REGEXP operator and its patterns

For a yes/no test, expr REGEXP pattern (synonyms: RLIKE, REGEXP_LIKE(expr, pattern)) returns 1 when the pattern matches anywhere in the text and 0 when it does not; NOT REGEXP reverses it. Unlike LIKE, the pattern does not have to match the whole value, so you add anchors yourself. All results below come from MySQL 8.4:

SQL
SELECT 'Hello' REGEXP '^he';            -- 1   ^ anchors at the start (case-insensitive by default collation)
SELECT 'Hello' RLIKE 'llo$';            -- 1   $ anchors at the end
SELECT 'Hello' NOT REGEXP 'z';          -- 1
SELECT 'cat' REGEXP 'cat|dog';          -- 1   | means "or"
SELECT 'gruy' REGEXP 'gr[^ae]y';        -- 1   [^...] is "any character except these"
SELECT 'ac' REGEXP '^ab?c$';            -- 1   ? is zero or one
SELECT 'abbc' REGEXP '^ab+c$';          -- 1   + is one or more
SELECT 'ac' REGEXP '^ab*c$';            -- 1   * is zero or more
SELECT 'abab' REGEXP '^(ab){2}$';       -- 1   (...) groups, {n} repeats exactly n times
SELECT 'ab' REGEXP '^.{2}$';            -- 1   . is any single character
SELECT 'a.b' REGEXP 'a\.b';            -- 1   \. matches a literal dot
SELECT 'night shift' REGEXP '\bshift\b';   -- 1   \b is a word boundary
SELECT 'abc123' REGEXP '^[[:alpha:]]+[[:digit:]]+$';   -- 1   POSIX classes inside brackets
Pattern Meaning
^ and $ Start and end of the text
. Any one character
[abc], [a-z], [^abc] One character from a set, a range, or not in the set
[[:alpha:]], [[:digit:]], [[:space:]], [[:upper:]] Named character classes
*, +, ?, , {n,m} Repetition of the item before it
a|b Alternation (either side)
( ) Grouping (and capturing for REGEXP_REPLACE)
\b, \w, \d Word boundary, word character, digit

Three details trip people up:

  • Escaping needs two backslashes. The string literal consumes one level of backslash before the regex engine sees the pattern, so a regex \. must be written '\.'. A single \b in a string literal is a backspace character and quietly never matches.
  • Case. With the default case-insensitive collation 'Hello' REGEXP '^h' is 1. For an exact-case test use REGEXP_LIKE('Hello', '^h', 'c') (0) or put COLLATE utf8mb4_bin after the pattern; i is the flag that forces case-insensitive matching. The old REGEXP BINARY form and the old word-boundary markers [[:<:]] and [[:>:]] from MySQL 5.7 now raise an error. Use \b instead.
  • Empty patterns (REGEXP '') are an error, and 'a+b' REGEXP 'a+b' treats + as "one or more a", so a literal plus is 'a\+b' or 'a[+]b'.

REGEXP_REPLACE accepts capture groups in the replacement with $1, $2: REGEXP_REPLACE('John Smith', '([a-z]+) ([a-z]+)', '$2, $1') returns Smith, John. REGEXP_INSTR('dog cat dog', 'dog', 2) returns 9 (the position of the second match when you start searching at position 2), and REGEXP_SUBSTR('abc123def456', '[0-9]+', 1, 2) returns 456, the second match.

More string functions you will meet

A few older or less common functions appear in a lot of existing code:

SQL
SELECT INSERT('Quadratic', 3, 4, 'What');   -- 'QuWhattic'  replace 4 characters starting at position 3
SELECT INSERT('Quadratic', 3, 0, '--');     -- 'Qu--adratic' length 0 inserts without removing
SELECT INSERT('Quadratic', 3, 100, 'What'); -- 'QuWhat'     a length past the end replaces the rest
SELECT INSERT('Quadratic', -1, 4, 'What');  -- 'Quadratic'  position outside the string: unchanged
SELECT CONCAT('[', LTRIM('  hi  '), ']');   -- '[hi  ]'   removes leading spaces only
SELECT CONCAT('[', RTRIM('  hi  '), ']');   -- '[  hi]'   removes trailing spaces only
SELECT TRIM(LEADING 'x' FROM 'xxabcxx');    -- 'abcxx'    TRIM can remove any character, from one end or both
SELECT UCASE('abc'), LCASE('ABC');          -- 'ABC', 'abc'  synonyms of UPPER and LOWER
SELECT REVERSE('abc'), REPEAT('ab', 3);     -- 'cba', 'ababab'
SELECT FORMAT(1234567.891, 2);              -- '1,234,567.89'  a string, for display only
SELECT STRCMP('a', 'b'), ASCII('A'), CHAR(72, 105);   -- -1, 65, 'Hi'

INSERT() here is a string function, not the INSERT statement; MySQL tells them apart by the parentheses. FORMAT returns text with thousands separators, so use it only for display and never feed its result back into arithmetic.

Date and time functions

MySQL's date toolbox is rich and uses named units (DAY, MONTH, HOUR...):

SQL
SELECT NOW(), CURDATE(), CURTIME();                        -- the current moment, date, time
SELECT DATE_ADD('2026-03-07', INTERVAL 10 DAY);            -- '2026-03-17'
SELECT DATE_SUB('2026-03-07', INTERVAL 1 MONTH);           -- '2026-02-07'
SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH);           -- '2026-02-28' (clamped to month end)
SELECT '2026-03-07' + INTERVAL 2 WEEK;                     -- '2026-03-21'
SELECT DATEDIFF('2026-03-01', '2026-01-01');               -- 59 (days, ignores time)
SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-03-14');   -- 1 (complete months only)
SELECT TIMESTAMPDIFF(HOUR, '2026-03-07 08:00:00', '2026-03-08 09:30:00');  -- 25
SELECT YEAR('2026-03-07'), MONTH('2026-03-07'), DAYOFWEEK('2026-03-07');   -- 2026, 3, 7
SELECT DATE_FORMAT('2026-03-07', '%W, %M %e, %Y');         -- 'Saturday, March 7, 2026'
SELECT DATE_FORMAT('2026-03-07 14:05:09', '%Y-%m-%d %H:%i');  -- '2026-03-07 14:05'
SELECT STR_TO_DATE('07/03/2026', '%d/%m/%Y');              -- '2026-03-07'
SELECT LAST_DAY('2026-02-10');                             -- '2026-02-28'

A few rules of thumb:

  • Argument order differs. DATEDIFF(later, earlier) gives days. TIMESTAMPDIFF(unit, earlier, later) takes the unit first and the earlier value before the later one. Swapping them flips the sign, a very common bug.
  • TIMESTAMPDIFF counts whole units. January 15 to March 14 is one complete month, not two.
  • Adding months clamps to the end of a short month. Adding one month to January 31 gives February 28 (or 29), never an invalid date.
  • Format codes are case-sensitive. %m is the month number but %M is the month name, %i is minutes (not %m), %H is a 24-hour clock and %h a 12-hour clock, %e is the day without a leading zero.
  • Index-friendly filters: compare the bare column with a range, WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01', instead of WHERE MONTH(created_at) = 3, which cannot use an index on the column (Query Optimization Techniques).

For the storage side (DATE, DATETIME, TIMESTAMP and time zones) see Date and Time Types.

SQLite stores dates as text and works with date(), datetime(), strftime() and julianday(). The modifiers are written as strings such as '+10 days':

SQL Dates, SQLite style

Press Run (or Ctrl + Enter) to see the result here.

The result shows the contrast clearly: SQLite's '+1 month' on January 31 overflows into March 3, while MySQL's DATE_ADD clamps to February 28. The range filter counts three logins in March, including the one at 22:59:59 on the 31st, because the upper bound is "before April 1" instead of "up to March 31".

Math functions

SQL
SELECT ROUND(2.567, 2), ROUND(2.5), ROUND(-2.5);   -- 2.57, 3, -3 (exact values round half away from zero)
SELECT ROUND(1234.567, -2);                        -- 1200
SELECT TRUNCATE(2.99, 1), TRUNCATE(-1.99, 1);      -- 2.9, -1.9 (cuts digits, never rounds)
SELECT CEIL(4.1), FLOOR(4.9), FLOOR(-4.1);         -- 5, 4, -5
SELECT 17 DIV 5, 17 MOD 5, 17 / 5;                 -- 3, 2, 3.4000
SELECT POW(2, 10), SQRT(144), ABS(-9);             -- 1024, 12, 9
SELECT RAND();                                     -- a random value in [0, 1)
SELECT GREATEST(3, 9, 5), LEAST(3, 9, 5);          -- 9, 3
  • ROUND versus TRUNCATE: ROUND(2.99, 1) is 3.0 and TRUNCATE(2.99, 1) is 2.9. Use TRUNCATE when you must never round up (for example, displaying a balance), and prefer ROUND for statistics.
  • Exact versus approximate numbers: ROUND(2.5) on a DECIMAL or integer literal gives 3. On a FLOAT/DOUBLE value MySQL uses the C library, which rounds halves to the nearest even number, so ROUND(2.5e0) is 2. For money, use DECIMAL (Numeric Types).
  • Division: / always returns a decimal result in MySQL (17 / 5 is 3.4000), DIV is integer division and division by zero returns NULL (an error in strict mode inside INSERT/UPDATE).
  • ORDER BY RAND() shuffles the whole table and is slow on large tables. Pick random rows another way when the table is big.
SQL Rounding and truncating in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQLite has no TRUNCATE function, so the example multiplies, casts to an integer (which cuts toward zero) and divides again. It also shows the integer-division trap: 17 / 5 is 3 in SQLite, while MySQL gives 3.4000 and needs DIV to get 3. Floating-point storage means 14.995 may not round the way you expect, one more reason to store money as DECIMAL in MySQL.

What ran live here

The live examples used SQLite's own functions (substr, instr, printf, date, strftime, julianday, round) to demonstrate each idea. All MySQL calls (CONCAT_WS, SUBSTRING_INDEX, the REGEXP_* family, DATE_ADD, TIMESTAMPDIFF, DATE_FORMAT, TRUNCATE, DIV) are read-only code blocks with the results MySQL returns.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does CONCAT('a', NULL, 'b') return in MySQL?

    CONCAT returns NULL if any argument is NULL. CONCAT_WS skips NULL arguments instead.
  2. 2. Which call returns the part of 'www.example.com' after the last dot?

    A negative count counts delimiters from the right, so -1 returns everything after the last dot, which is 'com'.
  3. 3. What does TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-03-14') return?

    TIMESTAMPDIFF counts only complete units. March 14 is one day short of two full months after January 15, so the result is 1.
  4. 4. Which filter can use an index on created_at?

    Comparing the bare column with a range is index-friendly. Wrapping the column in a function forces MySQL to evaluate every row.
  5. 5. What is the difference between ROUND(2.99, 1) and TRUNCATE(2.99, 1)?

    ROUND rounds to the nearest value, TRUNCATE just cuts off digits.

Summary

  • CONCAT returns NULL when any argument is NULL. CONCAT_WS skips NULL. || is logical OR in MySQL unless PIPES_AS_CONCAT is set.
  • SUBSTRING_INDEX, LEFT, RIGHT, LOCATE, LPAD and REPLACE cover most text cutting. REGEXP_REPLACE and friends handle patterns. CHAR_LENGTH counts characters, LENGTH counts bytes.
  • Use DATE_ADD/DATE_SUB with INTERVAL, DATEDIFF for days and TIMESTAMPDIFF for whole units. Watch argument order and format-code case in DATE_FORMAT.
  • Filter dates with ranges on the bare column so indexes work.
  • ROUND rounds, TRUNCATE cuts. Division with / gives decimals, DIV gives integers. Use DECIMAL for money.

Next: NULL Handling and Comparison Rules.