4️⃣ 🔧 MySQL Functions & Expressions – String, Date, Math, Conditional

---

🧲 Introduction – Why Use Functions & Expressions in MySQL?

In real-world applications, SQL isn’t just about fetching records—it’s about transforming, calculating, and evaluating data directly within your queries. MySQL offers a vast suite of built-in functions and expressions that simplify business logic, reporting, and data transformation.

🎯 In this guide, you’ll learn:

• Categories of MySQL functions (String, Date, Math, Logical)
• Syntax and examples of common built-in functions
• Use of expressions in conditional logic and arithmetic
• Best practices and real-world scenarios

---

📘 Topics Covered

🔧 Topic	📄 Description
⏱️ MySQL Date & Time Functions	Format, compare, and compute date/time values
🔢 MySQL Numeric Functions	Perform mathematical calculations inside queries
🔤 MySQL String Functions	Manipulate strings for formatting, searching, and parsing
➗ MySQL Arithmetic Operators	Basic mathematical operations on columns or literals
⚖️ Logical & Comparison Operators	Perform boolean logic and evaluate conditions
🚫 NULL Functions	Handle missing data using NULL-safe logic

---

🧠 What Are MySQL Functions & Expressions?

• 🧩 Functions are predefined routines that take inputs and return outputs.
• 🔗 Expressions are combinations of literals, operators, and functions that evaluate to a value.

These tools allow you to derive new values, validate logic, or compute results directly in SQL.

---

🔤 1. MySQL String Functions

Function	Description	Example Usage
CONCAT()	Join strings	CONCAT('Hello', ' World') → 'Hello World'
LENGTH()	String length in bytes	LENGTH('MySQL') → 5
LOWER()	Convert to lowercase	LOWER('HELLO') → 'hello'
UPPER()	Convert to uppercase	UPPER('world') → 'WORLD'
SUBSTRING()	Extract substring	SUBSTRING('SQLWorld', 1, 3) → 'SQL'

---

🗓️ 2. MySQL Date & Time Functions

Function	Description	Example Usage
NOW()	Current date and time	'2025-06-23 14:00:00'
CURDATE()	Current date only	'2025-06-23'
DATEDIFF()	Days between two dates	DATEDIFF('2025-07-01', '2025-06-01') → 30
DATE_FORMAT()	Format date to readable string	DATE_FORMAT(NOW(), '%Y-%m-%d')

---

➗ 3. MySQL Numeric Functions

Function	Description	Example Usage
ABS()	Absolute value	ABS(-10) → 10
ROUND()	Round to specific decimals	ROUND(5.789, 2) → 5.79
CEIL()	Next highest integer	CEIL(4.3) → 5
FLOOR()	Next lowest integer	FLOOR(4.8) → 4

---

❓ 4. MySQL Conditional Functions

Function	Description	Example Usage
IF()	Simple if-else	IF(5 > 3, 'Yes', 'No') → 'Yes'
IFNULL()	Replace NULL with a default	IFNULL(NULL, 'default') → 'default'
NULLIF()	Return NULL if equal	NULLIF(5, 5) → NULL
CASE	Multi-branch logic	See next section

---

🧮 Expressions – Logic & Calculations

➗ Arithmetic Expression

SELECT price * quantity AS total_price FROM orders;

✅ Computes the total price by multiplying fields.

🔤 String Expression

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

✅ Combines two fields into a full name.

❓ Conditional Expression (CASE)

SELECT name,
  CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 75 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

✅ Assigns a grade based on numeric conditions.

---

📘 Best Practices for Functions & Expressions

📌 Tip	✅ Reason
Use aliases (AS)	Improves query readability
Prefer built-in functions	Reduces application-side logic and boosts performance
Avoid deeply nested logic	Use subqueries or CTEs for maintainability
Watch for NULL pitfalls	Use IFNULL() or COALESCE() to handle NULLs
Format dates for output	Use DATE_FORMAT() for reports or dashboards

---

🚀 Real-World Use Cases

Use Case	Example Application
🧾 Invoice formatting	ROUND(total, 2) for currency precision
🧑‍💼 Full-name generation	CONCAT(first_name, ' ', last_name)
📅 Age calculation	TIMESTAMPDIFF(YEAR, dob, CURDATE())
🧪 Grade evaluation	CASE WHEN score >= 90 THEN 'A'...
📊 Monthly sales reports	DATE_FORMAT(order_date, '%Y-%m')

---

📌 Summary – Recap & Next Steps

Built-in functions and expressions in MySQL supercharge your queries by adding logic, formatting, and calculations directly in SQL. Whether it’s formatting a date, computing totals, or evaluating conditions, these features offer clean, powerful solutions.

🔍 Key Takeaways:

• Functions like CONCAT(), NOW(), ROUND(), and IFNULL() make SQL logic more expressive
• Expressions allow you to combine operators, columns, and functions for dynamic calculations
• Use CASE for multi-branch logic in reports or conditional queries

⚙️ Real-World Relevance
SQL functions are indispensable in reporting, automation, dashboards, and any application where business logic needs to live close to the data.

---

❓ FAQ – MySQL Functions & Expressions

❓ Can I use multiple functions in one query?
✅ Yes. Functions can be combined or nested:

SELECT UPPER(CONCAT(first_name, ' ', last_name)) AS name FROM employees;

---

❓ What’s the difference between IF, CASE, and IFNULL?

• IF() – binary condition
• CASE – multiple branches
• IFNULL() – replaces NULL with default

---

❓ Do string functions support Unicode?
✅ Yes, but check that your table and database use UTF-8 encoding.

---

❓ How can I prevent NULL issues in calculations?
✅ Use IFNULL() or COALESCE() to provide safe fallback values.

---

❓ Can I define my own custom functions?
✅ Yes. Use CREATE FUNCTION to define stored functions, though they differ from built-in functions.

---

Share Now :