MySQL Functions

Functions transform values inside a query. They help clean text, calculate dates, round numbers, and handle conditional logic.

String functions

SELECT
  CONCAT(first_name, " ", last_name) AS full_name,
  UPPER(city) AS city_upper,
  LOWER(country) AS country_lower
FROM customers;

Trim whitespace:

SELECT TRIM("  MySQL  ") AS cleaned_text;

Extract part of a string:

SELECT SUBSTRING("Data Engineering", 1, 4) AS short_name;

Date functions

SELECT
  order_id,
  order_date,
  YEAR(order_date) AS order_year,
  MONTH(order_date) AS order_month,
  DAY(order_date) AS order_day
FROM orders;

Current date and timestamp:

SELECT
  CURRENT_DATE() AS today,
  CURRENT_TIMESTAMP() AS now;

Date difference:

SELECT
  order_id,
  DATEDIFF(CURRENT_DATE(), order_date) AS days_since_order
FROM orders;

Numeric functions

SELECT
  total_amount,
  ROUND(total_amount, 0) AS rounded_amount,
  CEIL(total_amount) AS rounded_up,
  FLOOR(total_amount) AS rounded_down
FROM orders;

CASE

CASE creates conditional values.

SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount >= 1000 THEN "high"
    WHEN total_amount >= 500 THEN "medium"
    ELSE "low"
  END AS order_size
FROM orders;

COALESCE and IFNULL

COALESCE returns the first non-null value.

SELECT
  customer_id,
  COALESCE(phone_number, email, "no contact") AS preferred_contact
FROM customers;

IFNULL is a MySQL-specific shortcut for replacing one nullable value.

SELECT
  customer_id,
  IFNULL(phone_number, "missing") AS phone_number
FROM customers;

Use functions carefully in WHERE clauses. Applying a function to an indexed column can make the index harder for MySQL to use.