String, Number & Date Functions

Raw data is rarely in exactly the shape a report needs. Names are stored in two columns but the invoice wants one. Prices are stored without tax but the bill needs GST. Dates are stored as 2025-01-05 but the customer expects "05 Jan 2025". MySQL's built-in functions reshape values inside the query, so the result arrives ready to use.

A function takes one or more values (its arguments) in brackets and returns a new value. In this lesson you will use the most common text, number, NULL and date functions. Text and number examples run in the browser; MySQL's date functions do not exist in the browser's SQLite, so those examples show MySQL's output.

Text functions

CONCAT() joins pieces of text, UPPER() and LOWER() change case, and LENGTH() measures text:

SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       UPPER(city) AS city_caps,
       LOWER(state) AS state_lower,
       LENGTH(first_name) AS name_length
FROM customers
WHERE customer_id <= 4;

full_name    | city_caps | state_lower | name_length
-------------+-----------+-------------+------------
Aarav Sharma | ROHTAK    | haryana     | 5
Priya Verma  | JAIPUR    | rajasthan   | 5
Rohan Mehta  | MUMBAI    | maharashtra | 5
Ananya Iyer  | CHENNAI   | tamil nadu  | 6
(4 rows)

Functions can be nested: the result of one becomes the argument of another. Here INSTR() finds the position of @ in each email, and SUBSTR() cuts out everything before it. REPLACE() swaps one piece of text for another, and CONCAT_WS() ("with separator") joins values with the same separator between each:

SELECT email,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS username,
       REPLACE(email, '@example.com', '@shop.in') AS new_email,
       CONCAT_WS(', ', city, state) AS location
FROM customers
WHERE customer_id IN (2, 5, 8);

email                   | username    | new_email           | location
------------------------+-------------+---------------------+------------------
priya.verma@example.com | priya.verma | priya.verma@shop.in | Jaipur, Rajasthan
kabir.singh@example.com | kabir.singh | kabir.singh@shop.in | Ludhiana, Punjab
sneha.gupta@example.com | sneha.gupta | sneha.gupta@shop.in | Delhi, Delhi
(3 rows)

SUBSTR(text, start, length) counts positions from 1, not 0. MySQL also accepts the longer name SUBSTRING().

Two things to watch. First, in MySQL CONCAT() returns NULL if any argument is NULL (the browser treats NULL as empty text, so check this on MySQL); CONCAT_WS() skips NULL values instead. Second, MySQL's LENGTH() counts bytes, not characters. English letters take one byte, but Hindi letters and the ₹ sign take three in utf8mb4. Use CHAR_LENGTH() to count characters. MySQL also has LEFT(), RIGHT() and LPAD(), which the browser does not support:

SELECT LENGTH('नमस्ते') AS bytes,
       CHAR_LENGTH('नमस्ते') AS chars,
       LEFT('Rohtak', 3) AS short_city,
       LPAD(101, 6, '0') AS padded_id;

bytes | chars | short_city | padded_id
------+-------+------------+----------
18    | 6     | Roh        | 000101
(1 row)

LPAD() pads text on the left to a fixed width, which is handy for invoice numbers such as 000101.

Number functions

ROUND(x, d) rounds to d decimal places. FLOOR() rounds down to a whole number and CEIL() rounds up. Here the shop prices accessories with 18 percent GST, a 15 percent sale price rounded down, and a GST price rounded up to the next rupee:

SELECT product_name, price,
       ROUND(price * 1.18, 2) AS price_with_gst,
       FLOOR(price * 0.85) AS sale_price,
       CEIL(price * 1.18) AS rounded_up
FROM products
WHERE category = 'Accessories'
ORDER BY price;

product_name        | price | price_with_gst | sale_price | rounded_up
--------------------+-------+----------------+------------+-----------
Wireless Mouse      | 799   | 942.82         | 679        | 943
Laptop Stand        | 1299  | 1532.82        | 1104       | 1533
USB-C Hub           | 1899  | 2240.82        | 1614       | 2241
HD Webcam           | 2199  | 2594.82        | 1869       | 2595
Mechanical Keyboard | 3499  | 4128.82        | 2974       | 4129
Graphics Tablet     | 4599  | 5426.82        | 3909       | 5427
(6 rows)

MySQL shows price as 799.00; the browser shows 799. Other useful number functions are ABS() (remove the minus sign), POWER(x, y) (x to the power y), SQRT() (square root) and MOD(x, y) or the % operator (remainder after division; MOD() is MySQL-only).

Functions for NULL values

These functions replace or create NULL values, which is useful for reports and labels:

SELECT first_name,
       COALESCE(email, 'not given') AS email,
       IF(email IS NULL, 'Call', 'Email') AS contact_by
FROM customers
WHERE customer_id BETWEEN 3 AND 5;

first_name | email                   | contact_by
-----------+-------------------------+-----------
Rohan      | rohan.mehta@example.com | Email
Ananya     | not given               | Call
Kabir      | kabir.singh@example.com | Email
(3 rows)

• COALESCE(a, b, ...) returns the first value that is not NULL. IFNULL(a, b) is MySQL's two-argument version.
• IF(condition, then, else) picks one of two values. For more than two choices, use a CASE expression.
• NULLIF(a, b) returns NULL when the two values are equal. total / NULLIF(count, 0) avoids a division-by-zero problem.

Date and time functions (MySQL)

MySQL has a rich set of date functions. Two of them tell you the current moment: CURDATE() returns today's date and NOW() returns the current date and time. Their output depends on when you run them, so no output is shown:

SELECT CURDATE(), NOW();

SELECT order_id
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;   -- orders from the last 30 days

The next query formats each of Aarav's order dates, counts the days until 1 June 2025, adds seven days for a delivery promise, and names the weekday:

SELECT order_id,
       DATE_FORMAT(order_date, '%d %b %Y') AS nice_date,
       DATEDIFF('2025-06-01', order_date) AS days_to_june,
       DATE_ADD(order_date, INTERVAL 7 DAY) AS deliver_by,
       DAYNAME(order_date) AS day_name
FROM orders
WHERE customer_id = 1
ORDER BY order_date;

order_id | nice_date   | days_to_june | deliver_by | day_name
---------+-------------+--------------+------------+---------
101      | 05 Jan 2025 | 147          | 2025-01-12 | Sunday
104      | 02 Feb 2025 | 119          | 2025-02-09 | Sunday
115      | 20 May 2025 | 12           | 2025-05-27 | Tuesday
(3 rows)

• DATE_FORMAT(date, format) writes a date your way. Common codes: %d day (05), %e day without zero (5), %m month number, %b short month (Jan), %M full month (January), %Y four-digit year, %W weekday name, %H:%i hours and minutes.
• DATEDIFF(later, earlier) returns the number of days between two dates.
• DATE_ADD(date, INTERVAL n unit) and DATE_SUB() move a date forward or back by DAY, WEEK, MONTH or YEAR.
• YEAR(), MONTH() and DAY() pull out one part of a date as a number.

If you need the year or month in a query that also runs in the browser, SUBSTR(order_date, 1, 7) returns text such as 2025-01 in both databases. You will use this trick in the sales report project.

Functions in WHERE

You can use functions anywhere a value is allowed, including WHERE and ORDER BY. Be careful in WHERE: wrapping a column in a function, as in WHERE YEAR(order_date) = 2025, stops MySQL from using an index on that column. Compare the raw column with a range instead (see Indexes & EXPLAIN).

Key points

• Functions take arguments in brackets and return a new value; they can be nested.
• Text: CONCAT, CONCAT_WS, UPPER, LOWER, TRIM, SUBSTR, INSTR, REPLACE.
• MySQL's LENGTH() counts bytes; use CHAR_LENGTH() for characters in Hindi or other scripts.
• Numbers: ROUND, CEIL, FLOOR, ABS, POWER, SQRT; avoid dividing whole numbers in portable queries.
• COALESCE, IFNULL, IF and NULLIF handle NULL values.
• Dates (MySQL): CURDATE, NOW, DATE_FORMAT, DATEDIFF, DATE_ADD, YEAR, MONTH.

SELECT CONCAT(UPPER(last_name), ', ', first_name, ' (', city, ')') AS label,
       COALESCE(SUBSTR(email, INSTR(email, '@') + 1), 'none') AS email_domain
FROM customers
ORDER BY last_name;