SQL functions are built-in methods that simplify data manipulation, calculation, and formatting tasks in SQL. Below are the main types of SQL functions with examples:
1. Scalar Functions
- Returns a single value for each row.
| Function | Description | Example |
| UPPER() | Converts text to uppercase. | SELECT UPPER('hello') → HELLO |
| LOWER() | Converts text to lowercase. | SELECT LOWER('HELLO') → hello |
| LEN() | Returns string length. | SELECT LEN('SQL') → 3 |
| REPLACE() | Replaces text within a string. | SELECT REPLACE('Hello World', 'World', 'SQL') → Hello SQL |
| SUBSTRING() | Extracts part of a string. | SELECT SUBSTRING('Database', 1, 4) → Data |
| ROUND() | Rounds a numeric value. | SELECT ROUND(3.456, 2) → 3.46 |
| GETDATE() | Returns the current date and time. | SELECT GETDATE() → 2025-03-09 14:30:00 |
| ISNULL() | Replaces NULL values. | SELECT ISNULL(NULL, 'Default Value') → Default Value |
2. Aggregate Functions
- Performs calculations on multiple rows and returns a single result.
| Function | Description | Example |
| COUNT() | Counts the number of records. | SELECT COUNT(*) FROM Employees |
| SUM() | Calculates the total sum. | SELECT SUM(Salary) FROM Employees |
| AVG() | Returns the average value. | SELECT AVG(Salary) FROM Employees |
| MIN() | Returns the minimum value. | SELECT MIN(Salary) FROM Employees |
| MAX() | Returns the maximum value. | SELECT MAX(Salary) FROM Employees |
3. Date and Time Functions
- Manage
and manipulate date and time values.
| Function | Description | Example |
| GETDATE() | Returns the current date and time. | SELECT GETDATE() |
| DATEADD() | Adds or subtracts time intervals. | SELECT DATEADD(DAY, 5, GETDATE()) |
| DATEDIFF() | Returns the difference between dates. | SELECT DATEDIFF(YEAR, '2000-01-01', GETDATE()) |
| FORMAT() | Formats date values. | SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') |
4. String Functions
- Manipulate
text data.
| Function | Description | Example |
| CONCAT() | Combines two or more strings. | SELECT CONCAT('Hello', ' World') → Hello World |
| TRIM() | Removes spaces from both ends. | SELECT TRIM(' Hello ') → Hello |
| LEFT() | Extracts characters from the left side. | SELECT LEFT('Database', 4) → Data |
| RIGHT() | Extracts characters from the right side. | SELECT RIGHT('Database', 4) → base |
5. Mathematical Functions
- Perform
mathematical operations.
| Function | Description | Example |
| ABS() | Returns the absolute value. | SELECT ABS(-10) → 10 |
| CEILING() | Rounds up to the nearest integer. | SELECT CEILING(4.2) → 5 |
| FLOOR() | Rounds down to the nearest integer. | SELECT FLOOR(4.9) → 4 |
| POWER() | Raises a number to a given power. | SELECT POWER(2, 3) → 8 |
6. Conversion Functions
- Convert
data types.
| Function | Description | Example |
| CAST() | Converts data type explicitly. | SELECT CAST(123 AS VARCHAR) |
| CONVERT() | Converts data type with formatting options. | SELECT CONVERT(VARCHAR, GETDATE(), 103) → 09/03/2025 |
.png)
