More MySQL Functions

The MySQL functions a developer reaches for every week, grouped by job. Clean up and take apart text, round numbers, work with dates, and handle NULL and empty text.

Apprentice · SQL · MySQL · Updated

By

After this lesson you can

  • Clean up and take apart text in MySQL with TRIM, REPLACE, LEFT, RIGHT, SUBSTRING and LOCATE
  • Round numbers and work with dates in MySQL with CEILING, FLOOR, ROUND, DATEDIFF, DATE_ADD and DATE_FORMAT
  • Handle NULL and empty text in MySQL with IFNULL, NULLIF and CONCAT_WS

Key takeaways

  • TRIM and REPLACE clean text up; LEFT, RIGHT, SUBSTRING and LOCATE take it apart. SQL counts characters from 1, where Java counts from 0.
  • MySQL's / keeps the decimals: 39 / 12 is 3.2500. CEILING rounds up, FLOOR rounds down, and ROUND(2.5) gives 3.
  • DATEDIFF(later, earlier) counts the days between two dates, DATE_ADD moves a date by an INTERVAL, and DATE_FORMAT turns a date into text for people.
  • In MySQL, CONCAT gives NULL if any part is NULL, while CONCAT_WS skips the NULLs. IFNULL takes two values; COALESCE takes any number.
  • NULLIF(fax, '') turns empty text into NULL, so COALESCE(NULLIF(fax, ''), 'none') treats a blank and a NULL alike.

Common SQL Functions joined names with CONCAT() and filled in missing values with COALESCE(). Real requests soon ask for more. Here are four that could land on a Northwind developer's desk in a single week:

  • The new phone system wants each customer's phone number as digits only: (171) 555-7788 has to become 1715557788.
  • The email tool wants each contact's first name, so a message can start "Hi Maria".
  • The warehouse packs 12 units to a carton and wants to know how many cartons each product needs.
  • Accounting sends invoices due 30 days after the order and wants the due date printed as April 1, 2026.

You could read every row into Java and fix each value there, with the methods from Useful String Methods. But MySQL can hand you the finished values, and then every program that runs the query gets the same answer. MySQL has hundreds of built-in functions. This lesson teaches the ones a junior developer uses every week, grouped by the job they do.

A quick word first. A function takes the values in its parentheses, called its arguments, and gives back one new value. The functions in this lesson work on one row at a time: each row gets its own answer, and the table itself never changes.

Clean up text: TRIM, UPPER, LOWER and REPLACE

A customer types Chai into the search box on the store's site, with three stray spaces before it and two after it. WHERE product_name = ' Chai ' finds nothing, because to the database those spaces are characters like any other.

TRIM() removes the spaces at both ends of a piece of text. The square brackets below are there only so you can see that no spaces are left inside them:

SELECT CONCAT('[', TRIM('   Chai  '), ']') AS trimmed;
trimmed
[Chai]

When only one end should be cleaned, LTRIM() removes the spaces on the left (the start) and keeps the ones at the end, and RTRIM() removes the ones on the right (the end) and keeps the ones at the start. TRIM never touches the spaces in the middle: TRIM(' Aniseed Syrup ') keeps the space between the two words. Use it on what people type, before you compare it or save it. In Working with User Input the search value arrived as a ? parameter, so the query can clean it as it compares: WHERE product_name = TRIM(?).

UPPER() gives the text in capitals, and LOWER() in lower case: UPPER('Chai') is CHAI. They're for what people see, such as a country printed in capitals on a shipping label. You rarely need them to compare text: MySQL's comparisons ignore letter case by default, so WHERE country = 'germany' finds the customers in Germany.

REPLACE(text, find, put_instead) swaps every place find appears for put_instead. Say each product needs a web address, such as /products/aniseed-syrup: lower case, with a dash in place of each space.

SELECT product_name
    , LOWER(REPLACE(product_name, ' ', '-')) AS web_name
FROM products
ORDER BY product_id
LIMIT 4;
product_name web_name
Chai chai
Chang chang
Aniseed Syrup aniseed-syrup
Chef Anton's Cajun Seasoning chef-anton's-cajun-seasoning

This is a nested call: one function inside another. Read it from the inside out. REPLACE runs first and gives Aniseed-Syrup; then LOWER turns that into aniseed-syrup. The apostrophe in Anton's is still there. Another REPLACE wrapped around the outside could take it out too: you can nest as many as the job needs.

One difference from =: REPLACE matches letter case exactly. REPLACE('Chai', 'c', 'k') changes nothing, because Chai has a capital C, not a small one.

Take text apart: LEFT, RIGHT, SUBSTRING and LOCATE

Back to the email tool, which wants each contact's first name. In Useful String Methods you did this in Java: find the space with indexOf(' '), then take everything before it with substring(0, position). MySQL has a function for each step.

  • LOCATE(find, text) gives the position where find first appears in text.
  • LEFT(text, n) gives the first n characters, and RIGHT(text, n) the last n.
  • SUBSTRING(text, start) gives everything from position start to the end, and SUBSTRING(text, start, length) gives length characters from start.
SELECT contact_name
    , LOCATE(' ', contact_name) AS space_at
    , LEFT(contact_name, LOCATE(' ', contact_name) - 1) AS first_name
    , SUBSTRING(contact_name, LOCATE(' ', contact_name) + 1) AS last_name
FROM customers
ORDER BY customer_id
LIMIT 4;
contact_name space_at first_name last_name
Maria Anders 6 Maria Anders
Ana Trujillo 4 Ana Trujillo
Antonio Moreno 8 Antonio Moreno
Thomas Hardy 7 Thomas Hardy

SQL counts characters from 1, not from 0. In Maria Anders, the M is at position 1 and the space is at position 6, so the first name is the 5 characters before it: LEFT(contact_name, 6 - 1). Java's indexOf(' ') would say 5 for the same space, because Java starts counting at 0. When the text isn't there at all, LOCATE gives 0, where Java's indexOf gives -1.

The third argument of SUBSTRING is a length, not an end position as in Java's substring(start, end). A few calls side by side:

Call Gives Why
SUBSTRING('Northwind', 1, 5) North 5 characters, starting at the first
SUBSTRING('Northwind', 6) wind everything from position 6 to the end
SUBSTRING('Northwind', -4) wind a negative start counts from the end
SUBSTRING('Northwind', 0, 5) empty text there is no position 0
LEFT('Northwind', 5) North the first 5 characters
RIGHT('(171) 555-7788', 4) 7788 the last 4 characters

SUBSTR is another name for SUBSTRING in MySQL; they do the same thing. RIGHT is handy for the last digits of a phone number, as when support asks "is this the number ending in 7788?".

MySQL also has a shortcut for "the part before a separator": SUBSTRING_INDEX(text, separator, count). SUBSTRING_INDEX('Maria Anders', ' ', 1) gives everything before the first space, Maria, and a count of -1 gives everything after the last one, Anders.

Names are messy, so decide which answer the job needs. One Northwind contact is Isabel de Castro. Everything after the first space is de Castro, but SUBSTRING_INDEX(contact_name, ' ', -1) gives only the last word, Castro.

Like any value, a function's answer can be used in WHERE. The customers whose phone number starts with London's code, (171):

SELECT company_name, phone
FROM customers
WHERE LEFT(phone, 5) = '(171)';

Measure text: CHAR_LENGTH and LENGTH

The shipping labels have room for 20 characters of company name, so someone asks which names are too long. You need each name's length. MySQL has two functions for that, and they don't always agree:

SELECT CHAR_LENGTH('Folk och fä HB') AS characters
    , LENGTH('Folk och fä HB') AS bytes;
characters bytes
14 15

CHAR_LENGTH() counts characters, the way a person would: 14. LENGTH() counts bytes, the space the text takes up in storage. MySQL 8.4 stores text as utf8mb4, a way of writing every character from every language as numbers (in your textbook this is called a character encoding). In utf8mb4, plain English letters take 1 byte each, a letter like ä takes 2, and an emoji takes 4. So LENGTH gives 15 here.

For "how many letters", use CHAR_LENGTH. With English-only text the two agree, which is why the difference tends to surprise people the first time a customer's name has an accent. To find the long names: WHERE CHAR_LENGTH(company_name) > 20.

Round numbers: CEILING, FLOOR, ROUND and ABS

The warehouse packs 12 units to a carton. Chai has 39 units in stock: how many cartons is that?

If you've divided two ints in Java, you got a whole number back, with the remainder dropped. MySQL's / keeps the decimals: 39 / 12 is 3.2500. That's the exact answer, but nobody ships a quarter of a carton. Two functions turn it into a whole number:

  • CEILING() rounds up to the next whole number: the cartons you need to hold every unit. (CEIL() is another name for it.)
  • FLOOR() rounds down: the cartons you can fill completely.
SELECT product_name
    , units_in_stock
    , units_in_stock / 12 AS cartons_exact
    , CEILING(units_in_stock / 12) AS cartons_needed
    , FLOOR(units_in_stock / 12) AS full_cartons
FROM products
ORDER BY product_id
LIMIT 5;
product_name units_in_stock cartons_exact cartons_needed full_cartons
Chai 39 3.2500 4 3
Chang 17 1.4167 2 1
Aniseed Syrup 13 1.0833 2 1
Chef Anton's Cajun Seasoning 53 4.4167 5 4
Chef Anton's Gumbo Mix 0 0.0000 0 0

Chai's 39 units need 4 cartons: 3 full ones and a fourth holding the last 3 units. When you do want Java's kind of division, MySQL writes it DIV: 39 DIV 12 is 3.

ROUND(number, places) rounds to the nearest value with that many decimal places. Leave out places and it rounds to a whole number. A negative number of places rounds to the left of the decimal point, to tens or hundreds, as a sales report does with "about $1,200". TRUNCATE(number, places) cuts the extra digits off without rounding. ABS() gives a number without its minus sign: the distance from zero, whichever side it's on.

Call Gives Why
ROUND(18.4567, 2) 18.46 the 6 after 18.45 rounds it up
ROUND(18.4567) 18 no places: a whole number
ROUND(2.5) 3 a half rounds away from zero
ROUND(1234.567, -2) 1200 -2 places: to the nearest hundred
TRUNCATE(18.4567, 2) 18.45 cut off, not rounded
ABS(-16) 16 the distance from zero

The half-away-from-zero rule holds for exact numbers, such as prices stored as DECIMAL. A FLOAT or DOUBLE column stores numbers approximately, and ROUND can send its halves the other way. That's one reason money goes in DECIMAL columns.

Work with dates

Today

NOW() gives the date and time this moment, such as 2026-11-16 09:30:12, and CURDATE() gives only today's date, such as 2026-11-16. Both read the database server's clock, not the clock of the computer that sent the query.

Choose by what you're comparing. "The orders placed in the last 30 days" is about days, so it starts from CURDATE(): starting from NOW() at 9:30 in the morning would leave out the orders placed earlier on that first day.

SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

DATE_SUB moves a date back; it's coming up in a moment.

Pulling a date apart

YEAR(), MONTH() and DAY() give one part of a date as a number. DAYNAME() and MONTHNAME() give the names, and QUARTER() the quarter of the year, 1 to 4, which sales reports group by. For an order placed on Monday, March 2, 2026:

Call Gives
YEAR('2026-03-02') 2026
MONTH('2026-03-02') 3
DAY('2026-03-02') 2
DAYNAME('2026-03-02') Monday
MONTHNAME('2026-03-02') March
QUARTER('2026-03-02') 1

Counting the days between two dates

Every order has a required_date, the day the customer was promised it, and a shipped_date. Which orders shipped late, and by how many days?

DATEDIFF(later, earlier) counts the days from the second date to the first:

SELECT order_id
    , required_date
    , shipped_date
    , DATEDIFF(shipped_date, required_date) AS days_late
FROM orders
WHERE shipped_date > required_date;

Mind the order: the later date comes first. An order promised for March 16 that shipped on March 18 is DATEDIFF('2026-03-18', '2026-03-16'), which is 2. Swap the dates and you get -2. An order that shipped early, on March 10, gives DATEDIFF('2026-03-10', '2026-03-16'), which is -6: six days early. When the shipping manager wants "how many days off the promised date, early or late", ABS drops the sign: ABS(DATEDIFF(shipped_date, required_date)) is 6.

DATEDIFF counts days only. For whole months or years, use TIMESTAMPDIFF(unit, earlier, later), which takes its dates the other way around, earliest first. It counts complete units, so it's the right tool for an age:

Call Gives Why
TIMESTAMPDIFF(YEAR, '2000-12-08', '2026-11-16') 25 the 26th birthday, on December 8, hasn't come yet
YEAR('2026-11-16') - YEAR('2000-12-08') 26 subtracting the years ignores the birthday

Labeling each order "Late" or "On time" by a rule is a job for CASE, which The CASE / WHEN Statement teaches later in the course.

Moving a date

DATE_ADD(date, INTERVAL n unit) moves a date forward, and DATE_SUB moves it back. The unit is DAY, WEEK, MONTH or YEAR. Read INTERVAL 30 DAY aloud as "an interval of 30 days". The invoice due date, 30 days after the order:

Call Gives Why
DATE_ADD('2026-03-02', INTERVAL 30 DAY) 2026-04-01 30 days later, counting into April
DATE_SUB('2026-03-02', INTERVAL 7 DAY) 2026-02-23 a week earlier
DATE_ADD('2026-01-31', INTERVAL 1 MONTH) 2026-02-28 February has no 31st, so it stops at the month's last day
LAST_DAY('2026-02-10') 2026-02-28 the last day of that month

LAST_DAY suits invoices that are due at the end of the month.

Dates for people: DATE_FORMAT

DATE_FORMAT(date, pattern) turns a date into text in the shape you describe. Each % and a letter stands for one part of the date:

Call Gives
DATE_FORMAT('2026-04-01', '%M %e, %Y') April 1, 2026
DATE_FORMAT('2026-04-01', '%W') Wednesday
DATE_FORMAT('2026-04-01', '%a %b %e') Wed Apr 1
DATE_FORMAT('2026-04-01', '%m/%d/%Y') 04/01/2026

%M is the month's name, %e the day without a leading zero, %Y the four-digit year, %W the weekday's name, %a and %b the short weekday and month names, %m and %d the month and day with a leading zero.

The answer is text, not a date. Sort by it and April 1, 2026 comes before March 2, 2026, because A comes before M. So sort by the date column itself, and format only what people read. For a report or a file you export from the database, DATE_FORMAT is the right tool; for a page in your Java app, the app usually formats the date it reads.

Numbers have a formatter too: FORMAT(number, places) gives text with commas and rounded decimals, so FORMAT(1234.5, 2) is 1,234.50, and CONCAT(' , FORMAT(1234.5, 2)) is $1,234.50.

NULL and empty text: CONCAT_WS, IFNULL and NULLIF

CONCAT and NULL

The shipping label shows the customer's city and region on one line. Many customers have no region (it's NULL), and in MySQL, CONCAT gives NULL if any of its values is NULL. The whole line disappears:

SELECT company_name
    , CONCAT(city, ', ', region) AS with_concat
    , CONCAT_WS(', ', city, region) AS with_concat_ws
FROM customers
WHERE company_name = 'Alfreds Futterkiste'
    OR company_name = 'Bottom-Dollar Markets'
ORDER BY company_name;
company_name with_concat with_concat_ws
Alfreds Futterkiste NULL Berlin
Bottom-Dollar Markets Tsawassen, BC Tsawassen, BC

CONCAT_WS(separator, value, value, ...) ("concat with separator") puts the separator between the values and skips the NULLs. Alfreds Futterkiste gets Berlin, with no stray comma. Read the first argument as the glue: here, a comma and a space.

IFNULL beside COALESCE

You met COALESCE in Common SQL Functions: it returns the first of its values that isn't NULL. MySQL also has IFNULL(value, stand_in), which does the same with exactly two values: IFNULL(region, 'n/a') and COALESCE(region, 'n/a') give the same answers. COALESCE takes any number of values, as in COALESCE(region, country, 'unknown'), and it works in every database, so it's the one to reach for first.

A warning if you ever work in SQL Server: there, ISNULL(value, stand_in) is the stand-in function. MySQL also has an ISNULL(), but it does a different job. It takes one value and answers whether it's NULL: ISNULL(region) gives 1 for yes and 0 for no.

NULLIF: when empty text should count as NULL

A form that saves customers sometimes stores an empty fax number as '', empty text, instead of NULL. Now COALESCE(fax, 'none') shows none for some customers with no fax and a blank for others, because '' isn't NULL.

NULLIF(a, b) gives NULL when its two values are equal, and a when they aren't. So NULLIF(fax, '') turns empty text into NULL and leaves real numbers alone, and COALESCE can do the rest:

fax NULLIF(fax, '') COALESCE(NULLIF(fax, ''), 'none')
030-0076545 030-0076545 030-0076545
'' (empty) NULL none
NULL NULL none

Add TRIM for a fax saved as a few spaces: NULLIF(TRIM(fax), '').

MySQL also gives NULL instead of an error when a number is divided by zero: units_on_order / units_in_stock is NULL for a product with nothing in stock. Most databases stop with an error instead, so it's a good habit not to rely on it.

Which function for which job

The job MySQL Example Gives
Remove spaces at the ends TRIM TRIM(' Chai ') Chai
Swap one piece of text for another REPLACE REPLACE('555-7788', '-', '') 5557788
Capitals, lower case UPPER, LOWER UPPER('uk') UK
Find a position LOCATE LOCATE(' ', 'Maria Anders') 6
First or last characters LEFT, RIGHT RIGHT('030-0074321', 4) 4321
A piece from the middle SUBSTRING SUBSTRING('Northwind', 6) wind
How many characters CHAR_LENGTH CHAR_LENGTH('Chai') 4
Round up, round down CEILING, FLOOR CEILING(39 / 12) 4
Round to places ROUND ROUND(18.4567, 2) 18.46
Distance from zero ABS ABS(-6) 6
Today, now CURDATE, NOW CURDATE() today's date
Days between dates DATEDIFF DATEDIFF('2026-03-18', '2026-03-16') 2
Move a date DATE_ADD, DATE_SUB DATE_ADD('2026-03-02', INTERVAL 30 DAY) 2026-04-01
A date as text DATE_FORMAT DATE_FORMAT('2026-04-01', '%M %e, %Y') April 1, 2026
Join, skipping NULLs CONCAT_WS CONCAT_WS(', ', 'Berlin', NULL) Berlin
A stand-in for NULL IFNULL, COALESCE IFNULL(NULL, 'n/a') n/a
Empty text as NULL NULLIF NULLIF('', '') NULL

Do it: dial the customers' numbers

The new phone system dials digits only. Each customer's phone was typed in its own country's style, with parentheses, spaces, dashes and dots, and the system can't dial any of them.

The customers table (the Tables tab shows the SQL that makes it) has these rows:

company_name phone
Alfreds Futterkiste 030-0074321
Around the Horn (171) 555-7788
Bon app' 91.24.45.40
Bottom-Dollar Markets (604) 555-4729
Romero y tomillo (91) 745 6200
Simons bistro 31 12 34 56

For example, Around the Horn's (171) 555-7788 loses its (, its ), its space and its dash, and becomes 1715557788. The digits stay text, so a number that starts with 0, like Alfreds Futterkiste's, keeps its 0.

Your query should give exactly these rows, in this order:

company_name dial
Alfreds Futterkiste 0300074321
Around the Horn 1715557788
Bon app' 91244540
Bottom-Dollar Markets 6045554729
Romero y tomillo 917456200
Simons bistro 31123456

Your job: write one SELECT query that gives each customer's number, ready to dial.

  • Show two columns: company_name, then dial.
  • dial is the phone with every (, ), space, - and . taken out, and nothing else changed.
  • Use REPLACE. It works the same in MySQL, SQL Server and SQLite, the database this practice box runs.
  • Sort the rows by company_name, A to Z.

Practiced live on the lesson page: write the SQL right there and check it.

Check yourself

3 questions to try on the lesson page.

So what

These functions turn the messy values real tables hold into the clean ones your Java code and your reports need: digits ready to dial, first names, whole cartons, due dates. Knowing them means fewer loops in Java, and the same answer for every program that asks.

Sources

  • Oracle, String Functions and Operators, MySQL 8.4 Reference Manual: TRIM, REPLACE (case-sensitive), LOCATE (0 when not found), SUBSTRING (position 0 and negative positions), SUBSTRING_INDEX, LENGTH and CHAR_LENGTH, CONCAT and CONCAT_WS with NULL, FORMAT.
  • Oracle, Mathematical Functions, MySQL 8.4 Reference Manual: CEILING, FLOOR, ROUND (half away from zero for exact numbers), TRUNCATE, ABS.
  • Oracle, Arithmetic Operators, MySQL 8.4 Reference Manual: / keeps four more decimal places, DIV, division by zero gives NULL.
  • Oracle, Date and Time Functions, MySQL 8.4 Reference Manual: NOW, CURDATE, DATEDIFF, TIMESTAMPDIFF, DATE_ADD and the end of a month, LAST_DAY, DATE_FORMAT and its letters.
  • Oracle, Flow Control Functions, MySQL 8.4 Reference Manual: IFNULL and NULLIF.
  • Oracle, Comparison Functions and Operators, MySQL 8.4 Reference Manual: COALESCE, and MySQL's one-value ISNULL().
  • Oracle, The utf8mb4 Character Set, MySQL 8.4 Reference Manual: up to four bytes for each character.

Key terms

Cheat sheets