MySQL String, Numeric, and Date Functions

Functions in SQL expressions

MySQL functions take values as input and return a value that can be selected, filtered, sorted, or used in a calculation. Built-in functions are commonly grouped by what they do: string functions transform text, numeric functions perform calculations, and date/time functions work with temporal values. Functions make queries expressive, but their behavior depends on input types, NULL values, character sets, collations, and time-zone settings. Consult documentation for the deployed version when relying on less common behavior.

String functions include LOWER, UPPER, CONCAT, SUBSTRING, and TRIM. They are useful for presentation or controlled normalization. However, normalizing a value during every query does not necessarily enforce that the stored value is consistent. If an application needs case-insensitive uniqueness, choose a suitable collation or a deliberate normalized key and unique constraint rather than relying only on LOWER in one SELECT.

SELECT customer_id,
       LOWER(TRIM(email)) AS normalized_email
FROM customers;

Numeric functions and exactness

Numeric functions such as ROUND, ABS, and CEIL can help calculate or format values. Understand whether the input is exact DECIMAL or approximate floating point. Financial calculations generally use DECIMAL and an explicitly chosen rounding point. Rounding each line and then summing can differ from summing exact values and rounding once; the correct policy comes from the business domain. Also check how the database driver converts DECIMAL results, since some JavaScript clients return them as strings to preserve precision.

Date and time functions

Date functions can extract parts, format output, add intervals, or calculate differences. DATE can extract a calendar date, DATE_ADD can add an interval, and YEAR or MONTH can extract components. Use these functions with a clear time-zone model. DATETIME values do not receive the same automatic UTC conversion as TIMESTAMP values, and session time zones affect temporal behavior. Store and query instants consistently, and retain a zone identifier separately when the original local zone is part of the requirement.

SELECT order_id,
       DATE(created_at) AS order_date,
       ROUND(total, 2) AS rounded_total
FROM orders
WHERE created_at >= '2026-04-01'
  AND created_at <  '2026-05-01';

The half-open interval includes every timestamp in April, including fractional seconds, without guessing an end-of-day value. It is often preferable to filtering with DATE(created_at) = ..., because applying a function to an indexed column can prevent a simple range lookup. Always inspect the execution plan rather than assuming an index will or will not be used.

NULL propagation and return types

Many functions return NULL when an input is NULL, but exact behavior varies by function. A concatenation involving NULL may result in NULL, which may not be the intended display behavior. COALESCE can choose the first non-NULL input, but use it only when the fallback has sound domain meaning. Replacing an unknown amount with zero in a report changes the question being answered. Learn the broader rules in MySQL NULL handling.

Result types can also affect precision and comparisons. A function may return a type different from its input or cause implicit conversions in surrounding expressions. Give computed output columns clear aliases, test boundary cases, and avoid depending on a display-format string for later date arithmetic. Format values at the presentation boundary where possible, keeping database types available for comparisons and sorting.

Performance and sargable predicates

A predicate is often called sargable when the optimizer can use an index to locate matching values efficiently. Wrapping an indexed column in a function can prevent a simple index range scan, while transforming a constant or using an equivalent range may preserve index access. This is not an absolute rule: MySQL supports functional indexes in applicable versions and a query may still use another useful access path. Measure with EXPLAIN and representative data. Do not rewrite expressions based only on folklore.

Use functions where they clarify a real calculation, keep domain and time-zone semantics explicit, and test NULL, precision, and boundary behavior. Explore CASE expressions, data types, and query plans.