MySQL Functions: String, Date, and Aggregate Functions

Learn how MySQL functions transform values, calculate dates, format text, and summarize rows with string, date, and aggregate SQL examples.

What MySQL Functions Do

A MySQL function is a SQL expression that accepts zero or more inputs and produces a result. An input supplied to a function is called an argument. An argument can be a column, literal value, expression, or another function call.

Functions let MySQL process stored values while a query runs. Instead of retrieving raw data and transforming it in application code, you can format text, calculate dates, and produce summaries directly in SQL.

SELECT function_name(argument1, argument2, ...) AS result_name
FROM table_name;

An alias is a temporary name for a result column. The AS keyword makes calculated output easier to understand.

MySQL supplies many built-in functions, which are functions provided by MySQL and callable from SQL statements. MySQL can also support user-defined functions created or installed for a particular environment, but this lesson focuses on built-in functions.

Where Function Calls Can Appear

Function calls can be used in several parts of a query:

• SELECT: create or calculate output columns.
• WHERE: filter individual rows using a calculated value.
• ORDER BY: sort by a transformed or calculated expression.
• GROUP BY: form groups using an expression.
• HAVING: filter groups after aggregate calculations.

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM customers
WHERE LOCATE('@', email) > 0
ORDER BY full_name;

Main Categories of MySQL Functions

A scalar function returns one value for a particular input value or row. String and date functions are commonly scalar functions. An aggregate function combines values from multiple rows into one summary value.

String Functions

A string is a sequence of text characters. String functions are useful for display labels, cleanup, replacement, and text searches.

CONCAT: Join Text Values

CONCAT joins its arguments into one string. String literals, such as ' ', can be used to insert spaces or other separators.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

If any argument passed to CONCAT is NULL, the result commonly becomes NULL. NULL is SQL's marker for an unknown or missing value; it is not the same as an empty string.

-- A missing last_name can make full_name NULL
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

When missing text should be treated as empty text, use an explicit NULL-handling approach such as COALESCE where appropriate:

SELECT CONCAT(first_name, ' ', COALESCE(last_name, '')) AS full_name
FROM customers;

REPLACE: Substitute Text

REPLACE(text, old_text, new_text) returns the text after replacing occurrences of one substring with another.

SELECT REPLACE(description, 'basic', 'standard') AS revised_description
FROM products;

This can create cleaned or revised display values without changing the stored data. To permanently change stored data, use an appropriate UPDATE statement and test it carefully first.

FORMAT: Present Numbers as Text

FORMAT(number, decimal_places) formats a number with the requested number of decimal places and grouping separators. The result is intended for presentation and is text, not a numeric value for further arithmetic.

SELECT product_name, FORMAT(price, 2) AS formatted_price
FROM products;

LOCATE: Find a Substring

LOCATE(substring, text) returns the position of the first occurrence of a substring. Positions begin at 1. If the substring is not found, the result is 0.

SELECT email, LOCATE('@', email) AS at_sign_position
FROM customers;

This can test whether text contains a phrase:

SELECT product_name
FROM products
WHERE LOCATE('wireless', description) > 0;

Text comparisons can be affected by the column's character set and collation, which define how characters are stored and compared, including case-sensitivity rules.

Date and Time Functions

A temporal value is a date, time, datetime, timestamp, or related value. A DATE value represents a calendar date, while a DATETIME value includes both a date and a time.

DATE: Extract the Calendar Date

DATE(datetime_expression) extracts the date component from a datetime or timestamp-like value.

SELECT order_id, DATE(created_at) AS order_date
FROM orders;

For example, a value such as 2026-08-18 14:35:00 produces the date 2026-08-18.

DATEDIFF: Count Date-Boundary Differences

DATEDIFF(date1, date2) returns the number of days from the second date to the first date. It works at date precision rather than returning fractional days or elapsed hours.

SELECT order_id,
       DATEDIFF(CURDATE(), created_at) AS days_since_order
FROM orders;

Because DATEDIFF compares dates, time components do not provide hour or minute precision. If an exact elapsed duration matters, use a time-aware calculation suitable for the required precision.

DATE_ADD: Add an Interval

DATE_ADD adds a specified interval to a date or datetime. An interval is a quantity and unit, such as 7 DAY or 1 MONTH.

SELECT subscription_id,
       DATE_ADD(start_date, INTERVAL 30 DAY) AS expiry_date
FROM subscriptions;

DATE_ADD(date_expression, INTERVAL quantity unit)

Use date functions for reporting dates, deadlines, elapsed-day calculations, and future expiration dates. When using months or years, remember that calendar units do not all contain the same number of days.

Aggregate Functions

Aggregate functions operate across a set of rows and return one summary value for that set, or one summary value for each group created with GROUP BY.

Common Aggregate Functions

Most aggregate functions ignore NULL values. If there are no non-NULL values to process, the result for functions such as SUM, AVG, MIN, and MAX can be NULL. Always consider whether that behavior matches the report you need.

Summarize an Entire Table

SELECT
    COUNT(*) AS order_count,
    AVG(total_amount) AS average_order,
    SUM(total_amount) AS revenue,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders;

This query returns one row containing several summaries for the entire orders table.

Summarize Each Group with GROUP BY

GROUP BY divides rows into groups before aggregate functions are calculated. For example, each category can receive its own count and total.

SELECT
    category_id,
    COUNT(*) AS product_count,
    SUM(price) AS category_value
FROM products
GROUP BY category_id;

In a grouped query, every selected expression should either be aggregated or be included in the GROUP BY list. This keeps the meaning of each result row clear and avoids grouping errors.

Filter Groups with HAVING

HAVING filters groups after aggregation. Use it when the condition depends on an aggregate result.

SELECT
    category_id,
    COUNT(*) AS product_count,
    SUM(price) AS category_value
FROM products
GROUP BY category_id
HAVING SUM(price) > 1000;

WHERE filters individual rows before grouping. HAVING filters grouped results after aggregate calculations.

SELECT category_id, SUM(price) AS category_value
FROM products
WHERE active = 1
GROUP BY category_id
HAVING SUM(price) > 1000;

Nesting Functions and Naming Results

Functions can be nested when the output of one function becomes an argument to another. Keep nested expressions readable and give the final result an alias.

SELECT FORMAT(SUM(price), 2) AS formatted_category_value
FROM products;

Here, SUM calculates a numeric total and FORMAT turns that total into presentation text with two decimal places.

Function results can be affected by case rules, collation, data type conversion, and NULL handling. For example, formatting a number produces text, while summing a numeric column produces a numeric result. Check the data type and comparison rules when a result will be used in another expression.

NULL, Dates, and Function Troubleshooting

CONCAT Returns NULL

If CONCAT unexpectedly returns NULL, at least one supplied argument is likely NULL. Inspect the source columns and decide whether a missing value should remain unknown or be treated as empty text. Use a suitable NULL-handling function when needed.

COUNT(column) Is Smaller Than COUNT(*)

COUNT(column) excludes rows where that column is NULL. Use COUNT(*) when the goal is to count rows, and use COUNT(column) when the goal is to count present values.

Filtering an Aggregate with WHERE

A condition such as WHERE SUM(price) > 1000 does not work because WHERE runs before grouping. Put the aggregate condition in HAVING after GROUP BY.

Unexpected DATEDIFF Results

DATEDIFF measures date differences in whole days and does not calculate fractional days or hours. Choose a time-aware calculation if the exact elapsed time matters.

Performance Note: Functions and Indexes

Applying a function to an indexed column in a WHERE condition can sometimes prevent MySQL from using that index efficiently. For example, this query transforms the indexed column for every comparison:

SELECT order_id
FROM orders
WHERE DATE(created_at) = '2026-08-18';

When appropriate, compare the original datetime column with a range instead:

SELECT order_id
FROM orders
WHERE created_at >= '2026-08-18 00:00:00'
  AND created_at <  '2026-08-19 00:00:00';

The range preserves the time information and can make index use more likely. Confirm performance with your schema, indexes, data volume, and query plan.

Quick Reference

• CONCAT(a, b) joins text; a NULL argument commonly makes the result NULL.
• REPLACE(text, old, new) substitutes text occurrences.
• FORMAT(number, decimals) creates presentation text with decimal places and grouping separators.
• LOCATE(part, text) returns the first position of a substring, or 0 if it is absent.
• DATE(value) extracts a calendar date.
• DATEDIFF(a, b) returns the date-boundary difference in days.
• DATE_ADD(value, INTERVAL quantity unit) adds calendar or time intervals.
• AVG, SUM, MIN, and MAX summarize non-NULL values.
• COUNT(*) counts rows; COUNT(column) counts non-NULL column values.
• GROUP BY creates groups, and HAVING filters those groups after aggregation.