Querying Data›Extra· 35 min read

Built-in Functions: Text, Numbers & Dates

Clean, format and calculate inside your queries with MySQL’s string, numeric and date functions — no extra code needed.

What you will learn

Transform text with string functions (CONCAT, UPPER, SUBSTRING, REPLACE)
Round and calculate with numeric functions
Work with dates using NOW, DATEDIFF and DATE_FORMAT

Functions transform values as they come out

A built-in function takes a value and gives back a changed one — uppercased text, a rounded number, a formatted date. You call them right inside SELECT, so MySQL does the cleaning and formatting for you instead of your application code. They do not change what is stored; they only change what the query returns.

String functions

These work on text columns. The most common ones, with what each does:

Function	Does
`CONCAT(a, b)`	Joins pieces of text together
`UPPER(s)` / `LOWER(s)`	Changes case
`LENGTH(s)`	Number of characters
`SUBSTRING(s, start, len)`	Pulls out part of the text
`REPLACE(s, find, with)`	Swaps one piece of text for another
`TRIM(s)`	Removes spaces from both ends

A worked example: build a friendly greeting and a tidy email domain from a users table:

CONCAT, UPPER and SUBSTRING on the users table

SELECT
  CONCAT('Hi, ', name, '!')               AS greeting,
  UPPER(name)                             AS shout,
  SUBSTRING(email, 1, 4)                  AS email_start
FROM users;

Line by line: CONCAT('Hi, ', name, '!') glues the fixed text, the user’s name, and an exclamation mark into one string, labelled greeting. UPPER(name) returns the name in capitals. SUBSTRING(email, 1, 4) takes 4 characters starting at position 1 (MySQL counts text from 1, not 0). Each gets a friendly column name with AS.

Note: Output (for Asha, asha@x.com): greeting shout email_start Hi, Asha! ASHA asha The stored name is still “Asha” — these functions only changed the output. SUBSTRING(email, 1, 4) gave the first four letters, “asha”.

Numeric functions

These work on number columns: ROUND(n, places) rounds to a number of decimals, CEIL(n) rounds up, FLOOR(n) rounds down, ABS(n) gives the absolute (positive) value, and MOD(a, b) gives the remainder. A practical use — show a price with tax, rounded to 2 decimals:

Add 18% tax and round to 2 decimal places

SELECT
  title,
  price,
  ROUND(price * 1.18, 2) AS price_with_tax
FROM products;

price * 1.18 adds 18% tax (multiplying by 1.18). ROUND(..., 2) then trims the result to two decimal places so it looks like money instead of a long decimal. The original price column is unchanged — we just show a calculated one beside it.

Note: Output (for a product priced 100): title price pricewithtax Wireless Mouse 100 118.00 100 × 1.18 = 118, rounded to two decimals as 118.00. Without ROUND, a price like 249 would show 293.82000000000005 — rounding keeps it clean.

Date and time functions

Dates are where functions really shine. NOW() gives the current date-and-time, CURDATE() just the date. DATEDIFF(a, b) counts days between two dates. DATE_FORMAT(d, pattern) turns a raw date into a human-friendly string. Suppose our orders table has a created_at date column:

Format an order date and count days since it was placed

SELECT
  id,
  DATE_FORMAT(created_at, '%d %b %Y')      AS placed_on,
  DATEDIFF(NOW(), created_at)              AS days_ago
FROM orders;

Reading it: DATE_FORMAT(created_at, '%d %b %Y') reshapes a stored date like 2026-06-01 into 01 Jun 2026 — %d is the day, %b the short month name, %Y the four-digit year. DATEDIFF(NOW(), created_at) subtracts the order date from today and returns the number of days between them, labelled days_ago.

Note: Output (if today is 2026-06-13 and the order was placed 2026-06-01): id placedon daysago 101 01 Jun 2026 12 The raw stored date 2026-06-01 became the readable “01 Jun 2026”, and DATEDIFF worked out it was 12 days ago. These are the functions behind “Posted 12 days ago” labels you see in apps.

Tip: Functions can be nested and combined with everything else: ORDER BY DATEDIFF(NOW(), created_at), WHERE UPPER(name) = 'ASHA', or GROUP BY DATE_FORMAT(created_at, '%Y-%m') to total sales per month. They are the everyday tools for cleaning and reporting on data.

Q. Which function joins several pieces of text into one string?

Answer: CONCAT(a, b, c, …) joins its arguments into a single string. ROUND is numeric, DATEDIFF works on dates, and SUBSTRING extracts part of a string.

✍️ Practice

Use CONCAT to build a full-name or greeting column from two text columns.
Use DATE_FORMAT and DATEDIFF to show each row’s date nicely and how many days old it is.

🏠 Homework

Write a query that shows each product’s name in uppercase, its price rounded to 2 decimals with tax added, and label the columns clearly.