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
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
TRIMandREPLACEclean text up;LEFT,RIGHT,SUBSTRINGandLOCATEtake it apart. SQL counts characters from 1, where Java counts from 0.- MySQL's
/keeps the decimals:39 / 12is 3.2500.CEILINGrounds up,FLOORrounds down, andROUND(2.5)gives 3. DATEDIFF(later, earlier)counts the days between two dates,DATE_ADDmoves a date by anINTERVAL, andDATE_FORMATturns a date into text for people.- In MySQL,
CONCATgivesNULLif any part isNULL, whileCONCAT_WSskips theNULLs.IFNULLtakes two values;COALESCEtakes any number. NULLIF(fax, '')turns empty text intoNULL, soCOALESCE(NULLIF(fax, ''), 'none')treats a blank and aNULLalike.
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-7788has to become1715557788. - 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 wherefindfirst appears intext.LEFT(text, n)gives the firstncharacters, andRIGHT(text, n)the lastn.SUBSTRING(text, start)gives everything from positionstartto the end, andSUBSTRING(text, start, length)giveslengthcharacters fromstart.
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('