MySQL Built-in Functions – String, Math, Date, JSON, Conditional

Introduction – Why MySQL Functions Matter

MySQL functions are predefined operations you can use to transform, calculate, format, and analyze data directly in your SQL queries. From text formatting and number crunching to date manipulation and flow control, MySQL functions let you write powerful, efficient queries with minimal code.

These functions are the foundation of query logic for dashboards, reports, ETL pipelines, stored procedures, and real-time apps.

In this guide, you’ll learn:

• The most important MySQL built-in function categories
• Syntax and examples for string, math, date, and control functions
• Use cases that improve performance and reduce app-side processing
• How to use aggregate and JSON functions in MySQL

1. MySQL Function Categories

2. String Functions in MySQL

Examples

-- Concatenate first and last name
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Replace characters
SELECT REPLACE(phone, '-', '') FROM contacts;

-- Extract substring
SELECT SUBSTRING(email, 1, 5) FROM employees;

Tip: Always TRIM() data before comparisons or display.

3. Math Functions in MySQL

Examples

SELECT ROUND(price, 2) FROM products;
SELECT ABS(-42);
SELECT POWER(2, 4); -- → 16

Use math functions in reports, discounts, billing logic, and validations.

4. Date & Time Functions

Examples

SELECT CURDATE(); -- Current date
SELECT NOW();     -- Current timestamp
SELECT DATEDIFF(CURDATE(), birth_date) / 365 AS age FROM users;

Combine with INTERVAL and DATE_ADD() for flexible scheduling queries.

5. Aggregate Functions (GROUP BY)

Examples

SELECT department, COUNT(*) FROM employees GROUP BY department;
SELECT AVG(salary) FROM employees WHERE department = 'Sales';

These are key to building analytical dashboards and summaries.

6. Conditional Functions

Examples

SELECT IF(score >= 60, 'Pass', 'Fail') FROM students;

SELECT CASE
  WHEN rating >= 4 THEN 'Excellent'
  WHEN rating >= 3 THEN 'Good'
  ELSE 'Needs Improvement'
END AS feedback FROM reviews;

Use IFNULL() or COALESCE() to handle null values gracefully.

7. JSON Functions in MySQL 5.7+

Examples

SELECT JSON_EXTRACT(data, '$.address.city') FROM users;

SELECT JSON_ARRAY('red', 'green', 'blue');

SELECT JSON_OBJECT('name', name, 'email', email) FROM customers;

Powerful for apps storing structured data or interacting with APIs.

8. Metadata & Utility Functions

SELECT VERSION();     -- MySQL version
SELECT DATABASE();    -- Current database
SELECT USER();        -- Logged-in user

Useful in admin tools, logs, or multi-database apps.

9. Real-World Use Cases

Summary – Recap & Best Practices

MySQL functions are your go-to tools for query-side logic, formatting, calculations, and transformations. Using functions efficiently improves query performance, reduces app-side processing, and ensures better data consistency.

Key Takeaways:

• Learn which function types match your use case (text, math, JSON)
• Use conditional and date logic to simplify business rules
• JSON functions are powerful for modern, API-driven databases

Real-World Relevance:
Used in real-time web apps, data pipelines, dashboards, and ERP systems to deliver dynamic, smart SQL solutions.

FAQ – MySQL Functions

Are MySQL functions case-sensitive?

No, function names are case-insensitive. But string comparisons may be, depending on collation.

Can I create custom functions?

Yes, use CREATE FUNCTION to build your own stored functions.

What’s the difference between IF() and CASE?

IF() is simpler for binary logic. CASE is more flexible for multi-branch conditions.

Are JSON functions available in all MySQL versions?

No. JSON support starts in MySQL 5.7+.