🔧 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

Category	Examples	Purpose
🔤 String	CONCAT(), SUBSTRING(), REPLACE()	Format and extract string values
🔢 Math	ROUND(), ABS(), CEIL()	Perform calculations and rounding
🗓️ Date/Time	NOW(), CURDATE(), DATEDIFF()	Handle dates and timestamps
📊 Aggregate	SUM(), AVG(), COUNT()	Summarize grouped data
❓ Conditional	IF(), CASE, IFNULL()	Control logic and handle NULLs
📦 JSON	JSON_EXTRACT(), JSON_ARRAY()	Parse and manipulate JSON data
🧠 Information	VERSION(), DATABASE(), USER()	Get metadata about the current session

---

🔤 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

Scenario	Function(s) Used
Normalize names	UPPER(), LOWER(), TRIM()
Calculate age from birth date	DATEDIFF(), CURDATE()
Dynamic discount logic	IF(), CASE, ROUND()
Create JSON response	JSON_OBJECT(), JSON_ARRAY()
Null-safe summaries	COALESCE(), IFNULL(), SUM()

---

📌 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+.

---

Share Now :