Functions

MySQL provides a large library of built-in functions for transforming and analyzing data within queries. These functions let you format output, perform arithmetic, manipulate strings, work with dates, and implement conditional logic without requiring application-level post-processing.

String functions

Parsing with SUBSTRING_INDEX

SUBSTRING_INDEX(string, delimiter, count) returns the part of a string before (count > 0) or after (count < 0) the nth occurrence of a delimiter. This is commonly applied to split delimited values stored in a single column:

SELECT emp_name,
   SUBSTRING_INDEX(emp_name, ' ', 1) AS first_name,
   SUBSTRING_INDEX(emp_name, ' ', -1) AS last_name
FROM string_sample;

The positive 1 returns everything before the first space. The negative -1 returns everything after the last space.

Finding character positions with LOCATE

LOCATE(substring, string[, start]) returns the position of the first occurrence of a substring. Pass a start position to search from a specific character:

SELECT emp_name,
   LOCATE(' ', emp_name) AS first_space,
   LOCATE(' ', emp_name, LOCATE(' ', emp_name) + 1) AS second_space
FROM string_sample;

The second call nests LOCATE inside itself to find the second space by starting the search after the first space.

Extracting substrings with SUBSTRING

SUBSTRING(string, start[, length]) extracts part of a string starting at position start. Combine it with LOCATE to extract name components at dynamic positions:

SELECT emp_name,
   SUBSTRING(emp_name, 1, LOCATE(' ', emp_name) - 1) AS first_name,
   SUBSTRING(emp_name, LOCATE(' ', emp_name) + 1) AS last_name
FROM string_sample;

The first call extracts from the beginning to the character before the first space. The second call extracts from the character after the first space to the end.

String joining with CONCAT

CONCAT(string1[, string2]...) joins two or more strings into one. Apply it to combine separate columns into a formatted output:

SELECT vendor_name,
   CONCAT(vendor_city, ', ', vendor_state, ' ', vendor_zip_code) AS address
FROM vendors;

LEFT

LEFT(string, n) extracts the first n characters from the left of a string. Apply it to generate initials or truncated codes:

SELECT vendor_contact_first_name, vendor_contact_last_name,
   CONCAT(LEFT(vendor_contact_first_name, 1),
      LEFT(vendor_contact_last_name, 1)) AS initials
FROM vendors;

Numeric functions

The following table summarizes common numeric functions with example results:

Searching floating-point columns

Because FLOAT and DOUBLE values are approximate, exact equality comparisons may not return the expected rows. Instead, search within a range:

SELECT *
FROM float_sample
WHERE float_value BETWEEN 0.99 AND 1.01;

Alternatively, filter on the rounded value:

SELECT *
FROM float_sample
WHERE ROUND(float_value, 2) = 1.00;

Date and time functions

Current date and time

The following functions return the current date or time based on the system clock:

Parsing date and time components

The following functions extract individual components from a date or time value:

EXTRACT function

EXTRACT(unit FROM datetime) extracts a specified unit from a date or time value and returns an integer. The following examples show available units:

DATE_FORMAT

DATE_FORMAT(date, format) formats a date value using a format string. Prefix each format code with %. The following format codes are available:

The following examples demonstrate DATE_FORMAT output:

Date and time arithmetic

DATE_ADD and DATE_SUB add or subtract an interval from a date. DATEDIFF returns the number of days between two dates:

Searching for dates and times

To retrieve rows within a date range:

SELECT *
FROM date_sample
WHERE start_date >= '2018-02-28' AND start_date < '2018-03-01';

To retrieve rows for a specific month, day, and year using extraction functions:

SELECT *
FROM date_sample
WHERE MONTH(start_date) = 2
   AND DAYOFMONTH(start_date) = 28
   AND YEAR(start_date) = 2018;

To retrieve rows matching a formatted date string:

SELECT *
FROM date_sample
WHERE DATE_FORMAT(start_date, '%m-%d-%Y') = '02-28-2018';

To retrieve rows matching an exact time:

SELECT * FROM date_sample
WHERE DATE_FORMAT(start_date, '%T') = '10:00:00';

To retrieve rows in a range of times:

SELECT * FROM date_sample
WHERE EXTRACT(HOUR_MINUTE FROM start_date) BETWEEN 900 AND 1200;

Conditional functions

CASE

CASE returns a value based on a set of conditions. When MySQL finds a WHEN expression that matches the test expression, it returns the corresponding THEN value. This is the SQL equivalent of an if-else chain and is commonly applied to categorize or label data in the result set.

The following classifies invoices by how overdue they are:

SELECT invoice_number, invoice_total, invoice_date, invoice_due_date,
   CASE
      WHEN DATEDIFF(NOW(), invoice_due_date) > 30
         THEN 'Over 30 days past due'
      WHEN DATEDIFF(NOW(), invoice_due_date) > 0
         THEN '1 to 30 days past due'
      ELSE 'Current'
   END AS invoice_status
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0;

IF

IF(condition, true_value, false_value) returns one value if the condition is true and another if it is false:

SELECT vendor_name,
   IF(vendor_city = 'Fresno', 'Yes', 'No') AS is_city_fresno
FROM vendors;

IFNULL

IFNULL(expression, replacement) returns the expression if it is not null, and the replacement value if it is. Apply this to substitute a displayable value for null in reports:

SELECT payment_date,
   IFNULL(payment_date, 'No Payment') AS new_date
FROM invoices;

COALESCE

COALESCE(expr1, expr2, ...) returns the first non-null expression in the list. It is more flexible than IFNULL when multiple fallback values are needed:

SELECT payment_date,
   COALESCE(payment_date, 'No Payment') AS new_date
FROM invoices;

Regular expression functions

MySQL provides dedicated functions for regular expression matching, searching, and replacement. These are more powerful than the REGEXP operator used in WHERE clauses and can appear anywhere in the SELECT list.

The following finds all vendor cities that contain a space:

SELECT DISTINCT vendor_city, REGEXP_INSTR(vendor_city, ' ') AS space_index
FROM vendors
WHERE REGEXP_INSTR(vendor_city, ' ') > 0
ORDER BY vendor_city;

The following returns cities starting with SAN or LOS:

SELECT vendor_city, REGEXP_SUBSTR(vendor_city, '^SAN|LOS') AS city_match
FROM vendors
WHERE REGEXP_SUBSTR(vendor_city, '^SAN|LOS') IS NOT NULL;

The following replaces the word STREET with St in address data:

SELECT vendor_name, vendor_address1,
   REGEXP_REPLACE(vendor_address1, 'STREET', 'St') AS new_address1
FROM vendors
WHERE REGEXP_LIKE(vendor_address1, 'STREET');