MySQL Functions

Overview

MySQL provides a rich set of built-in functions for data manipulation, string operations, mathematical calculations, date/time handling, and more.

Function Categories

• String Functions: Text manipulation
• Numeric Functions: Math calculations
• **Date
• Aggregate Functions: Data summarization
• Control Flow Functions: Logic
• JSON Functions: JSON data manipulation
• Window Functions: Analytical calculations

String Functions

Basic String Operations

-- Length
SELECT LENGTH('Hello World');  -- 11
SELECT CHAR_LENGTH('Hello');       -- 5

-- Concatenation
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World
SELECT 'Hello' || ' ' || 'World';       -- Hello World
SELECT CONCAT_WS(' ', 'Hello', 'World'); -- Hello World

-- Substring
SELECT SUBSTRING('Hello World', 1, 5);  -- Hello
SELECT MID('Hello World', 1, 5);          -- Hello
SELECT LEFT('Hello World', 5);             -- Hello
SELECT RIGHT('Hello World', 5);            -- World

-- Position
SELECT POSITION('World' IN 'Hello World');  -- 7
SELECT LOCATE('World', 'Hello World');    -- 7

String Trimming

-- Remove whitespace
SELECT TRIM('  Hello  ');           -- Hello
SELECT LTRIM('  Hello');               -- Hello
SELECT RTRIM('Hello  ');                -- Hello

-- Remove specific characters
SELECT TRIM(BOTH 'x' FROM 'xxxHelloxxx'); -- Hello
SELECT TRIM(LEADING 'x' FROM 'xxxHello');  -- Hello
SELECT TRIM(TRAILING 'x' FROM 'Helloxxx');  -- Hello

String Case

-- Convert case
SELECT UPPER('hello');    -- HELLO
SELECT LOWER('HELLO');    -- hello
SELECT UCASE('hello');     -- HELLO
SELECT LCASE('HELLO');     -- hello

-- Capitalize (manual)
SELECT CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS capitalized
FROM users;

String Replacement

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

-- Multiple replacements
SELECT REPLACE(REPLACE('abc123', 'abc', 'xyz'), '123', '456');  -- xyz456

Numeric Functions

Basic Math Operations

-- Rounding
SELECT ROUND(3.14159, 2);       -- 3.14
SELECT FLOOR(3.9);             -- 3
SELECT CEILING(3.1);            -- 4
SELECT TRUNCATE(3.999, 2);     -- 3.99

-- Absolute value
SELECT ABS(-10);               -- 10

-- Modulus
SELECT MOD(17, 5);             -- 2
SELECT 17 % 5;                  -- 2

Advanced Math

-- Power
SELECT POW(2, 8);               -- 256

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

-- Random
SELECT RAND();                  -- Random between 0 and 1
SELECT FLOOR(RAND() * 100);    -- Random integer 0-99

-- Sign
SELECT SIGN(-10);               -- -1
SELECT SIGN(10);                -- 1

Date

Current Date

-- Current date/time
SELECT NOW();                   -- 2024-01-15 10:30:00
SELECT CURRENT_TIMESTAMP();        -- 2024-01-15 10:30:00
SELECT SYSDATE();                -- 2024-01-15 10:30:00

-- Current date
SELECT CURDATE();               -- 2024-01-15
SELECT CURRENT_DATE();            -- 2024-01-15

-- Current time
SELECT CURTIME();               -- 10:30:00
SELECT CURRENT_TIME();            -- 10:30:00

Date Extraction

-- Extract components
SELECT YEAR('2024-01-15');         -- 2024
SELECT MONTH('2024-01-15');        -- 1
SELECT DAY('2024-01-15');          -- 15
SELECT HOUR('2024-01-15 10:30:00');  -- 10
SELECT MINUTE('10:30:00');           -- 30
SELECT SECOND('10:30:00');           -- 0

Date Arithmetic

-- Add/subtract intervals
SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY);     -- 2024-01-22
SELECT DATE_SUB('2024-01-15', INTERVAL 1 MONTH);  -- 2023-12-15

-- Date difference
SELECT DATEDIFF('2024-01-22', '2024-01-15');  -- 7 days
SELECT TIMESTAMPDIFF(DAY, '2024-01-22', '2024-01-15');  -- 7

-- Complex calculation
SELECT 
    DATE_ADD(CURDATE(), INTERVAL 30 DAY) AS due_date,
    DATEDIFF(DATE_ADD(CURDATE(), INTERVAL 30 DAY), CURDATE()) AS days_until_due;

Date Formatting

-- Format date
SELECT DATE_FORMAT('2024-01-15', '%Y-%m-%d');       -- 2024-01-15
SELECT DATE_FORMAT(NOW(), '%W');                   -- Week number
SELECT DATE_FORMAT(NOW(), '%a');                   -- Day name
SELECT TIME_FORMAT('10:30:00', '%h:%i %p');    -- 10:30 AM

Aggregate Functions

Count Functions

-- Count rows
SELECT COUNT(*) FROM users;              -- Total rows
SELECT COUNT(email) FROM users;        -- Non-null emails

-- Count distinct
SELECT COUNT(DISTINCT email) FROM users;  -- Unique emails

Sum and Average

-- Sum values
SELECT SUM(price) FROM products;           -- Total price
SELECT SUM(DISTINCT price) FROM orders;    -- Sum of unique prices

-- Average
SELECT AVG(price) FROM products;           -- Average price
SELECT AVG(DISTINCT price) FROM orders;    -- Average of unique prices

Min and Max

-- Minimum
SELECT MIN(price) FROM products;           -- Lowest price
SELECT MIN(created_at) FROM users;        -- Earliest date

-- Maximum
SELECT MAX(price) FROM products;           -- Highest price
SELECT MAX(created_at) FROM users;        -- Latest date

Grouping

-- Group by column
SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Multiple grouping
SELECT category, supplier, COUNT(*) AS product_count
FROM products
GROUP BY category, supplier;

Control Flow Functions

IF / IF

-- Simple IF
SELECT IF(1 > 0, 'True', 'False');  -- True

-- Nested IF
SELECT IF(score >= 90, 'A',
       IF(score >= 80, 'B',
       IF(score >= 70, 'C', 'D'))) AS grade
FROM students;

CASE / CASE

-- Simple CASE
SELECT 
    CASE 
        WHEN status = 'active' THEN 'Active User'
        WHEN status = 'inactive' THEN 'Inactive User'
        ELSE 'Unknown'
    END AS status_text
FROM users;

-- Searched CASE
SELECT 
    CASE 
        WHEN price < 50 THEN 'Cheap'
        WHEN price < 100 THEN 'Medium'
        WHEN price < 200 THEN 'Expensive'
        ELSE 'Very Expensive'
    END AS price_range
FROM products;

IFNULL and COALESCE

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

-- COALESCE (first non-null)
SELECT COALESCE(NULL, NULL, 'value', 'default');  -- value
SELECT COALESCE(phone, mobile_phone, 'No phone') FROM users;

JSON Functions

JSON Extraction

-- Extract values
SELECT 
    data->>'$.name' AS name,
    data->>'$.age' AS age,
    data->>'$.city' AS city
FROM users;

-- JSON_EXTRACT
SELECT JSON_EXTRACT(data, '$.name') AS name FROM users;

-- Unquote
SELECT JSON_UNQUOTE(data->>'$.name') AS name FROM users;

JSON Manipulation

-- Create JSON
SELECT JSON_OBJECT('name', 'John', 'age', 30);  -- {"name": "John", "age": 30}
SELECT JSON_ARRAY(1, 2, 3);              -- [1, 2, 3]

-- Modify JSON
SELECT JSON_SET(data, '$.age', 31);           -- Update value
SELECT JSON_INSERT(data, '$.email', 'test@test.com');  -- Add value
SELECT JSON_REPLACE(data, '$.age', 31);      -- Replace value
SELECT JSON_REMOVE(data, '$.email');          -- Remove value

JSON Validation

-- Validate JSON
SELECT JSON_VALID('{"name": "John"}');   -- 1 (true)
SELECT JSON_VALID('invalid json');         -- 0 (false)

-- Check type
SELECT JSON_TYPE('{"name": "John"}');     -- OBJECT
SELECT JSON_TYPE('[1, 2, 3]');           -- ARRAY

Window Functions

RANK Functions

-- RANK
SELECT 
    id,
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Aggregate Over

-- Running total
SELECT 
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average
SELECT 
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM daily_sales;

Practical Examples

Data Cleaning

-- Clean phone numbers
SELECT 
    REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone
FROM users;

-- Format names
SELECT 
    CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS formatted_name
FROM users;

Data Transformation

-- Calculate age
SELECT 
    birth_date,
    TIMESTAMPDIFF(YEAR, CURDATE(), birth_date) AS age
FROM users;

-- Calculate business days
SELECT 
    order_date,
    shipped_date,
    DATEDIFF(shipped_date, order_date) - 
        (DATEDIFF(shipped_date, order_date) / 7 * 2) AS business_days
FROM orders;

Data Analysis

-- Customer segment
SELECT 
    user_id,
    SUM(total) AS total_spent,
    COUNT(*) AS order_count,
    CASE 
        WHEN SUM(total) > 1000 THEN 'VIP'
        WHEN SUM(total) > 500 THEN 'Regular'
        ELSE 'New'
    END AS segment
FROM orders
GROUP BY user_id;

-- Daily statistics
SELECT 
    DATE(created_at) AS order_date,
    COUNT(*) AS order_count,
    SUM(total) AS daily_total,
    AVG(total) AS avg_order_value
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

Summary

MySQL functions provide:

• String Functions: Text manipulation and formatting
• Numeric Functions: Mathematical calculations
• **Date
• Aggregate Functions: Data summarization
• Control Flow: Conditional logic
• JSON Functions: JSON data handling
• Window Functions: Advanced analytics

Use functions for efficient data transformation and analysis.

---

Previous: Import Data

Next: MySQL Operators