## 🏗️ **What are Functions?**
In MySQL, a function is a pre-defined piece of code that accepts input (parameters), performs a specific action, and returns a result.
### **1. Single Row Functions (Scalar Functions)**
These functions operate on a **single row** at a time and return **one result per row**.
* *Example:* If you use `UPPER(Name)` on a table with 5 rows, you get 5 capitalized names back.
* *Types:* String, Numeric, Date/Time functions.
### **2. Multiple Row Functions (Aggregate Functions)**
These functions operate on a **group of rows** and return a **single result** for the whole group.
* *Example:* `SUM(Salary)` adds up salaries from all rows and gives just one total number.
* *(Note: This guide focuses on Single Row functions as requested).*
---
## 🛠️ **The Setup: Sample Table**
To make these examples easy to understand, we will use a single hypothetical table named **`EMPLOYEES`**.
| EmpID | Name | Salary | Bonus | Rating | JoinDate |
| --- | --- | --- | --- | --- | --- |
| 101 | **Alice** | 50000.50 | 1200.75 | 4.5 | 2023-05-15 |
| 102 | **Bob** | 65000.00 | 500.00 | -2.0 | 2021-11-20 |
| 103 | **Cathy** | 45000.25 | 0.00 | 3.0 | 2024-01-10 |
| 104 | **David** | 72000.90 | 2100.50 | 5.0 | 2020-12-25 |
---
## 🔤 **I. Text / String Functions**
These functions manipulate text data.
### 1. `CHAR()`
* **Purpose:** Returns the character for each integer passed (based on ASCII values).
* **Syntax:** `CHAR(N, ...)`
* **Example:**
```sql
SELECT CHAR(65, 66, 67) AS Result;
```
* **Output:** `ABC` (65=A, 66=B, 67=C)
### 2. `CONCAT()`
* **Purpose:** Joins two or more strings together.
* **Syntax:** `CONCAT(str1, str2, ...)`
* **Scenario:** Joining Name and "is an employee".
```sql
SELECT CONCAT(Name, ' works here') AS Statement FROM EMPLOYEES WHERE EmpID=101;
```
* **Output:** `Alice works here`
* *Note:* If any argument is `NULL`, the result is `NULL`.
### 3. `LOWER()` / `LCASE()`
* **Purpose:** Converts string to lowercase.
* **Syntax:** `LOWER(str)`
* **Example:**
```sql
SELECT LOWER(Name) FROM EMPLOYEES WHERE EmpID=102;
```
* **Output:** `bob`
### 4. `UPPER()` / `UCASE()`
* **Purpose:** Converts string to uppercase.
* **Syntax:** `UPPER(str)`
* **Example:**
```sql
SELECT UPPER(Name) FROM EMPLOYEES WHERE EmpID=101;
```
* **Output:** `ALICE`
### 5. `SUBSTR()` / `MID()` / `SUBSTRING()`
* **Purpose:** Extracts a portion of a string.
* **Syntax:**
1. `SUBSTR(str, pos, len)` -> Start at `pos`, take `len` characters.
2. `SUBSTR(str, pos)` -> Start at `pos`, take everything until the end.
* **Scenario A (With Length):**
```sql
SELECT SUBSTR(Name, 2, 3) FROM EMPLOYEES WHERE Name='Alice';
```
* **Output:** `lic` (Starts at 2nd char 'l', takes 3 chars).
* **Scenario B (No Length):**
```sql
SELECT MID(Name, 3) FROM EMPLOYEES WHERE Name='David';
```
* **Output:** `vid` (Starts at 3rd char 'v' to end).
### 6. `LTRIM()`, `RTRIM()`, `TRIM()`
* **Purpose:** Removes whitespace.
* `LTRIM`: Removes from Left (leading).
* `RTRIM`: Removes from Right (trailing).
* `TRIM`: Removes from Both sides.
* **Scenario:** Imagine a messy input `' Data '`.
```sql
SELECT LTRIM(' Data '), RTRIM(' Data '), TRIM(' Data ');
```
* **Output:** `'Data '`, `' Data'`, `'Data'`
### 7. `INSTR()`
* **Purpose:** Returns the position of the *first occurrence* of a substring in a string. Returns 0 if not found.
* **Syntax:** `INSTR(string, substring)`
* **Scenario:** Find 'a' in 'David'.
```sql
SELECT INSTR(Name, 'a') FROM EMPLOYEES WHERE Name='David';
```
* **Output:** `2` (Because 'a' is the 2nd letter).
### 8. `LENGTH()`
* **Purpose:** Returns the length of the string in bytes.
* **Syntax:** `LENGTH(str)`
* **Example:**
```sql
SELECT LENGTH(Name) FROM EMPLOYEES WHERE Name='Bob';
```
* **Output:** `3`
### 9. `LEFT()` / `RIGHT()`
* **Purpose:** Extracts a specified number of characters from the left or right side.
* **Syntax:** `LEFT(str, N)` or `RIGHT(str, N)`
* **Example:**
```sql
SELECT LEFT(Name, 2), RIGHT(Name, 1) FROM EMPLOYEES WHERE Name='Alice';
```
* **Output:** `Al` (First 2 chars), `e` (Last 1 char).
---
## 🧮 **II. Numeric Functions**
These functions perform mathematical operations on numbers.
### 1. `MOD()`
* **Purpose:** Returns the remainder of a division.
* **Syntax:** `MOD(N, M)` or `N % M`
* **Scenario:** Checking if Salary is divisible by 1000.
```sql
SELECT MOD(10, 3);
```
* **Output:** `1` (10 divided by 3 leaves a remainder of 1).
### 2. `POWER()` / `POW()`
* **Purpose:** Returns the argument raised to the specified power.
* **Syntax:** `POWER(X, Y)` (X to the power of Y).
* **Example:**
```sql
SELECT POWER(Rating, 2) FROM EMPLOYEES WHERE EmpID=103;
```
* **Output:** `9.0` (3.0 squared).
### 3. `SQRT()`
* **Purpose:** Returns the square root of a non-negative number.
* **Syntax:** `SQRT(N)`
* **Example:**
```sql
SELECT SQRT(25);
```
* **Output:** `5`
### 4. `SIGN()`
* **Purpose:** Returns the sign of a number.
* Returns `1` if number > 0.
* Returns `-1` if number < 0.
* Returns `0` if number = 0.
* **Example:**
```sql
SELECT SIGN(Rating) FROM EMPLOYEES WHERE EmpID=102;
```
* **Output:** `-1` (Since rating is -2.0).
### 5. `ROUND()` (Very Important for Exams!)
* **Purpose:** Rounds a number to a specified number of decimal places.
* **Syntax:** `ROUND(Number, Decimals)`
* **Scenario A (Positive Decimals):** Rounds to nearest decimal.
```sql
SELECT ROUND(50000.567, 2);
```
* **Output:** `50000.57` (6 rounds up to 7).
* **Scenario B (Zero Decimals):** Rounds to nearest integer.
```sql
SELECT ROUND(50000.50, 0);
```
* **Output:** `50001`
* **Scenario C (Negative Decimals):** Rounds to the left of the decimal point (tens, hundreds).
```sql
SELECT ROUND(125.50, -1);
```
* **Output:** `130` (Rounds to nearest 10).
### 6. `TRUNCATE()`
* **Purpose:** Truncates (cuts off) a number to a specified number of decimals *without rounding*.
* **Syntax:** `TRUNCATE(Number, Decimals)`
* **Scenario A (Standard):**
```sql
SELECT TRUNCATE(50000.567, 2);
```
* **Output:** `50000.56` (Simply drops the 7, no rounding).
* **Scenario B (Negative Decimals):**
```sql
SELECT TRUNCATE(125.50, -1);
```
* **Output:** `120` (Makes the ones place 0).
---
## 📅 **III. Date & Time Functions**
These functions work with date values. Assume today is **2023-12-25** (Monday) for "Current" examples.
### 1. `NOW()` / `SYSDATE()`
* **Purpose:** Returns current date and time.
* **Syntax:** `NOW()`
* **Example:**
```sql
SELECT NOW();
```
* **Output:** `2023-12-25 10:30:45` (Format: YYYY-MM-DD HH:MM:SS)
### 2. `CURDATE()` / `CURRENT_DATE()`
* **Purpose:** Returns only the current date.
* **Syntax:** `CURDATE()`
* **Example:**
```sql
SELECT CURDATE();
```
* **Output:** `2023-12-25`
### 3. `DATE()`
* **Purpose:** Extracts the date part from a datetime expression.
* **Syntax:** `DATE(expression)`
* **Example:**
```sql
SELECT DATE('2023-12-25 10:30:45');
```
* **Output:** `2023-12-25`
### 4. `DAY()` / `DAYOFMONTH()`
* **Purpose:** Returns the day of the month (1-31).
* **Syntax:** `DAY(date)`
* **Example:**
```sql
SELECT DAY(JoinDate) FROM EMPLOYEES WHERE EmpID=101; -- Date: 2023-05-15
```
* **Output:** `15`
### 5. `MONTH()`
* **Purpose:** Returns the month number (1-12).
* **Syntax:** `MONTH(date)`
* **Example:**
```sql
SELECT MONTH(JoinDate) FROM EMPLOYEES WHERE EmpID=101; -- Date: 2023-05-15
```
* **Output:** `5`
### 6. `YEAR()`
* **Purpose:** Returns the year (YYYY).
* **Syntax:** `YEAR(date)`
* **Example:**
```sql
SELECT YEAR(JoinDate) FROM EMPLOYEES WHERE EmpID=101;
```
* **Output:** `2023`
### 7. `DAYNAME()`
* **Purpose:** Returns the name of the weekday (Sunday, Monday...).
* **Syntax:** `DAYNAME(date)`
* **Example:**
```sql
SELECT DAYNAME('2023-12-25');
```
* **Output:** `Monday`
### 8. `MONTHNAME()`
* **Purpose:** Returns the name of the month (January, February...).
* **Syntax:** `MONTHNAME(date)`
* **Example:**
```sql
SELECT MONTHNAME(JoinDate) FROM EMPLOYEES WHERE EmpID=101;
```
* **Output:** `May`
### 9. `DAYOFWEEK()`
* **Purpose:** Returns the weekday index (1 = Sunday, 2 = Monday, ..., 7 = Saturday). *Note: This is the ODBC standard.*
* **Syntax:** `DAYOFWEEK(date)`
* **Example:**
```sql
SELECT DAYOFWEEK('2023-12-25'); -- It is a Monday
```
* **Output:** `2`
### 10. `DAYOFYEAR()`
* **Purpose:** Returns the day of the year (1-366).
* **Syntax:** `DAYOFYEAR(date)`
* **Example:**
```sql
SELECT DAYOFYEAR('2023-02-01');
```
* **Output:** `32` (31 days in Jan + 1st day of Feb).
---
## ⚡ **Cheat Sheet: ROUND vs TRUNCATE**
This is the most common pitfall in Class 12 exams.
| Value | Function | Result | Reason |
| --- | --- | --- | --- |
| **15.79** | `ROUND(15.79, 1)` | **15.8** | 9 is >= 5, so 7 becomes 8. |
| **15.79** | `TRUNCATE(15.79, 1)` | **15.7** | Simply cuts off after 7. |
| **15.29** | `ROUND(15.29, 1)` | **15.3** | 9 is >= 5, so 2 becomes 3. |
| **15.29** | `TRUNCATE(15.29, 1)` | **15.2** | Simply cuts off after 2. |
Back to List
Calculating...