MySQL Functions

⚡ What are MySQL Functions?

MySQL functions are built-in operations that perform calculations, manipulate strings, handle dates, and process data. They help you transform and analyze data efficiently without writing complex code.


-- Using a simple function
SELECT UPPER('hello world');
-- Result: HELLO WORLD

Function Categories

🔤

String Functions

Manipulate text and characters

SELECT CONCAT('Hello', ' ', 'World');

🔢

Numeric Functions

Perform mathematical operations

SELECT ROUND(15.75, 1);

📅

Date Functions

Work with dates and times

SELECT NOW();

📊

Aggregate Functions

Calculate summary statistics

SELECT COUNT(*) FROM users;

🔹 String Functions

String functions help you manipulate text data by changing case, combining strings, extracting substrings, finding text length, and replacing characters. These are essential for formatting output, cleaning data, and searching within text fields in your database tables.

-- Convert to uppercase
SELECT UPPER('hello');  -- Result: HELLO

-- Convert to lowercase
SELECT LOWER('WORLD');  -- Result: world

-- Concatenate strings
SELECT CONCAT('John', ' ', 'Doe');  -- Result: John Doe

-- Get string length
SELECT LENGTH('MySQL');  -- Result: 5

-- Extract substring
SELECT SUBSTRING('Hello World', 1, 5);  -- Result: Hello

-- Replace text
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- Result: Hello MySQL

🔹 Numeric Functions

Numeric functions perform mathematical calculations on number values. You can round decimals, find absolute values, calculate powers and square roots, generate random numbers, and get ceiling or floor values. These functions are crucial for financial calculations, statistical analysis, and data processing.

-- Round a number
SELECT ROUND(15.75);  -- Result: 16
SELECT ROUND(15.75, 1);  -- Result: 15.8

-- Absolute value
SELECT ABS(-25);  -- Result: 25

-- Power function
SELECT POW(2, 3);  -- Result: 8

-- Square root
SELECT SQRT(16);  -- Result: 4

-- Random number (0 to 1)
SELECT RAND();

-- Ceiling and floor
SELECT CEIL(4.3);   -- Result: 5
SELECT FLOOR(4.9);  -- Result: 4

🔹 Date and Time Functions

Date functions allow you to work with temporal data effectively. Get current date and time, extract specific parts like year or month, calculate date differences, add or subtract time intervals, and format dates for display. These are vital for scheduling, reporting, and time-based queries.

-- Current date and time
SELECT NOW();           -- Result: 2024-01-15 14:30:45
SELECT CURDATE();       -- Result: 2024-01-15
SELECT CURTIME();       -- Result: 14:30:45

-- Extract date parts
SELECT YEAR(NOW());     -- Result: 2024
SELECT MONTH(NOW());    -- Result: 1
SELECT DAY(NOW());      -- Result: 15

-- Date difference
SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- Result: 365

-- Add/subtract dates
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- Format date
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');  -- Result: 2024-01-15

🔹 Aggregate Functions

Aggregate functions compute single values from multiple rows of data. Count records, calculate sums and averages, find minimum and maximum values, and group data for analysis. These functions are fundamental for generating reports, statistics, and business intelligence from your database.

-- Count rows
SELECT COUNT(*) FROM employees;

-- Sum values
SELECT SUM(salary) FROM employees;

-- Average value
SELECT AVG(salary) FROM employees;

-- Minimum value
SELECT MIN(salary) FROM employees;

-- Maximum value
SELECT MAX(salary) FROM employees;

-- Group by example
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department;

🔹 Conditional Functions

Conditional functions enable decision-making logic within your SQL queries. Use IF statements for simple conditions, CASE for multiple conditions, IFNULL to handle null values, and COALESCE to return the first non-null value. These functions make your queries more flexible and intelligent.

-- IF function
SELECT IF(salary > 50000, 'High', 'Low') as salary_level
FROM employees;

-- CASE statement
SELECT name,
  CASE 
    WHEN salary > 70000 THEN 'Senior'
    WHEN salary > 50000 THEN 'Mid'
    ELSE 'Junior'
  END as level
FROM employees;

-- IFNULL function
SELECT IFNULL(phone, 'No phone') FROM users;

-- COALESCE function
SELECT COALESCE(phone, email, 'No contact') FROM users;

🔹 Conversion Functions

Conversion functions transform data from one type to another. Cast values to different data types, convert strings to dates, format numbers as strings, and ensure data compatibility. These functions are essential when working with mixed data types or preparing data for specific operations.

-- CAST function
SELECT CAST('123' AS UNSIGNED);  -- String to number
SELECT CAST(123.45 AS CHAR);     -- Number to string

-- CONVERT function
SELECT CONVERT('2024-01-15', DATE);

-- STR_TO_DATE function
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');

-- Format numbers
SELECT FORMAT(1234567.89, 2);  -- Result: 1,234,567.89

🔹 NULL Handling Functions

NULL handling functions help you manage missing or undefined data gracefully. Check for null values, replace nulls with default values, and handle null comparisons properly. Proper null handling prevents errors and ensures your queries return meaningful results even with incomplete data.

-- Check for NULL
SELECT * FROM users WHERE email IS NULL;

-- IFNULL - replace NULL with value
SELECT name, IFNULL(phone, 'N/A') as phone FROM users;

-- COALESCE - return first non-NULL
SELECT COALESCE(mobile, phone, email, 'No contact') FROM users;

-- NULLIF - return NULL if equal
SELECT NULLIF(status, 'inactive') FROM accounts;