MySQL Functions
MySQL has many built-in functions that change or calculate values inside a query, so your app has less work to do.
The examples use the original shop data from the introduction.
Text Functions
| Function | Result |
|---|---|
CONCAT('Rs ', 450) | Rs 450 |
UPPER('karachi') | KARACHI |
LOWER('KARACHI') | karachi |
LENGTH('Tea') | 3 (bytes) |
CHAR_LENGTH('Tea') | 3 (characters) |
SUBSTRING('Power Bank', 1, 5) | Power |
REPLACE('USB Cable', 'Cable', 'Charger') | USB Charger |
TRIM(' Ali ') | Ali |
CONCAT joins text together. In MySQL the || sign does not join text by default, so always use CONCAT.
Example
SELECT CONCAT(name, ' (', category, ')') AS product
FROM products
WHERE stock > 50;+--------------------------+ | product | +--------------------------+ | Cooking Oil 1L (Grocery) | | USB Cable (Electronics) | +--------------------------+ 2 rows in set (0.00 sec)
For Urdu or other non-English text, use CHAR_LENGTH, not LENGTH. One Urdu letter takes 2 bytes in utf8mb4, so LENGTH would count it twice.
Number Functions
| Function | Result |
|---|---|
ROUND(1650.456, 2) | 1650.46 |
ROUND(1650.456) | 1650 |
CEIL(4.1) | 5 |
FLOOR(4.9) | 4 |
ABS(-20) | 20 |
MOD(10, 3) | 1 |
Example
SELECT name, price, ROUND(price * 1.18, 2) AS price_with_tax
FROM products
WHERE category = 'Electronics';+------------+---------+----------------+ | name | price | price_with_tax | +------------+---------+----------------+ | USB Cable | 450.00 | 531.00 | | Power Bank | 3800.00 | 4484.00 | +------------+---------+----------------+ 2 rows in set (0.00 sec)
This adds 18% sales tax to each price.
Date and Time Functions
| Function | Returns |
|---|---|
NOW() | Current date and time, like 2026-10-15 11:20:05 |
CURDATE() | Today's date, like 2026-10-15 |
YEAR(d), MONTH(d), DAY(d) | Parts of a date |
DAYNAME('2026-10-15') | Thursday |
DATE_ADD('2026-10-15', INTERVAL 7 DAY) | 2026-10-22 |
DATEDIFF('2026-10-15', '2026-10-01') | 14 (days between) |
DATE_FORMAT('2026-10-15', '%d %M %Y') | 15 October 2026 |
Example
SELECT id, customer,
DATE_FORMAT(order_date, '%d %b %Y') AS ordered_on
FROM orders
WHERE order_date >= '2026-10-02';+----+----------+-------------+ | id | customer | ordered_on | +----+----------+-------------+ | 2 | Ali | 02 Oct 2026 | | 3 | Bilal | 02 Oct 2026 | | 4 | Ayesha | 05 Oct 2026 | +----+----------+-------------+ 3 rows in set (0.00 sec)
Common DATE_FORMAT codes: %d day, %m month number, %M month name, %b short month name, %Y four-digit year, %H:%i hours and minutes.
Conditional Functions
IFNULL replaces a NULL with another value: IFNULL(phone, 'No phone'). IF picks one of two values:
Example
SELECT name, IF(stock > 0, 'Available', 'Sold out') AS status
FROM products
WHERE category <> 'Grocery';+------------+-----------+ | name | status | +------------+-----------+ | USB Cable | Available | | Power Bank | Available | | School Bag | Sold out | +------------+-----------+ 3 rows in set (0.00 sec)
For more than two choices, use CASE:
Example
SELECT name, stock,
CASE
WHEN stock = 0 THEN 'Out of stock'
WHEN stock < 20 THEN 'Low'
ELSE 'OK'
END AS stock_level
FROM products;+------------------+-------+--------------+ | name | stock | stock_level | +------------------+-------+--------------+ | Basmati Rice 5kg | 40 | OK | | Cooking Oil 1L | 75 | OK | | Tea 950g | 30 | OK | | USB Cable | 120 | OK | | Power Bank | 15 | Low | | School Bag | 0 | Out of stock | +------------------+-------+--------------+ 6 rows in set (0.00 sec)
Functions with Joins and Groups
Functions combine with joins and GROUP BY. How much has each customer spent?
Example
SELECT o.customer,
COUNT(*) AS orders,
SUM(o.quantity * p.price) AS spent
FROM orders AS o
JOIN products AS p ON p.id = o.product_id
GROUP BY o.customer
ORDER BY spent DESC;+----------+--------+---------+ | customer | orders | spent | +----------+--------+---------+ | Ayesha | 2 | 8000.00 | | Bilal | 1 | 3800.00 | | Ali | 1 | 1350.00 | +----------+--------+---------+ 3 rows in set (0.00 sec)
Exercise
Exercise
Which function call returns the text 'Rs 620'?