Functions in SQL allow for more sophisticated data manipulation and retrieval, enhancing the capability of your queries. This article covers various types of functions: Aggregate Functions, NULL Functions, String Functions, Mathematical Functions, and Date Functions.
Table of Contents
1. Aggregate Functions
Aggregate functions are designed to calculate a single value from a set of values, making them essential for summarizing data. They are often used in conjunction with the “GROUP BY" clause to organize and analyze grouped data.
| Function | Description | Example |
| MIN() | Returns the smallest value in a set. | SELECT MIN(Salary) FROM Employees; |
| MAX() | Returns the largest value in a set. | SELECT MAX(Salary) FROM Employees; |
| COUNT() | Returns the number of rows that match a specified condition. | SELECT COUNT(*) FROM Employees WHERE Department = ‘Sales’; |
| SUM() | Returns the total sum of a numeric column. | SELECT SUM(Salary) FROM Employees; |
| AVG() | Returns the average value of a numeric column. | SELECT AVG(Salary) FROM Employees; |
| GROUP_CONCAT() (MySQL) | Concatenates values from multiple rows into a single string. | SELECT GROUP_CONCAT(FirstName) FROM Employees; |
| ARRAY_AGG() (PostgreSQL) | Returns an array of values. | SELECT ARRAY_AGG(FirstName) FROM Employees; |
2. NULL Functions
NULL functions in SQL are used to manage NULL values, offering ways to check for their presence or substitute them with alternative values.
| Function | Description | Example |
| ISNULL() (SQL Server) | Checks if a value is NULL. | SELECT ISNULL(Email, ‘No Email’) FROM Employees; |
| COALESCE() | Returns the first non-null value in a list. | SELECT COALESCE(Email, ‘No Email’) FROM Employees; |
| NULLIF() | Returns NULL if two expressions are equal; otherwise, returns the first expression. | SELECT NULLIF(Salary, 0) FROM Employees; |
| IFNULL() (MySQL) | Returns the first argument if it’s not NULL, otherwise returns the second argument. | SELECT IFNULL(Email, ‘No Email’) FROM Employees; |
| NVL() (Oracle) | Similar to COALESCE, returns the first argument if not NULL, otherwise returns the second argument. | SELECT NVL(Email, ‘No Email’) FROM Employees; |
3. String Functions
String functions are used to process and manipulate textual data, enabling a wide range of operations on strings.
| Function | Description | Example |
| CONCAT() | Concatenates two or more strings. | SELECT CONCAT(FirstName, ‘ ‘, LastName) FROM Employees; |
| SUBSTRING() | Extracts a substring from a string. | SELECT SUBSTRING(FirstName, 1, 3) FROM Employees; |
| LEN() / LENGTH() | Returns the length of a string. | SELECT LEN(FirstName) FROM Employees; (SQL Server) SELECT LENGTH(FirstName) FROM Employees; (MySQL/PostgreSQL) |
| TRIM() | Removes leading and trailing spaces from a string. | SELECT TRIM(FirstName) FROM Employees; |
| UPPER() | Converts a string to uppercase. | SELECT UPPER(FirstName) FROM Employees; |
| LOWER() | Converts a string to lowercase. | SELECT LOWER(FirstName) FROM Employees; |
| CHARINDEX() (SQL Server) | Returns the starting position of a specified expression in a string. | SELECT CHARINDEX(‘o’, FirstName) FROM Employees; |
| REPLACE() | Replaces all occurrences of a specified string value with another string value. | SELECT REPLACE(FirstName, ‘o’, ‘a’) FROM Employees; |
| LEFT() | Returns the left part of a string with a specified number of characters. | SELECT LEFT(FirstName, 2) FROM Employees; |
| RIGHT() | Returns the right part of a string with a specified number of characters. | SELECT RIGHT(FirstName, 2) FROM Employees; |
4. Mathematical Functions
Mathematical functions are utilized to carry out various calculations and operations on numerical data.
| Function | Description | Example |
| ROUND() | Rounds a numeric field to the specified number of decimal places. | SELECT ROUND(Salary, 2) FROM Employees; |
| CEIL() / CEILING() | Rounds a number up to the nearest integer. | SELECT CEIL(Salary) FROM Employees; |
| FLOOR() | Rounds a number down to the nearest integer. | SELECT FLOOR(Salary) FROM Employees; |
| ABS() | Returns the absolute value of a number. | SELECT ABS(Salary) FROM Employees; |
| POWER() | Returns the value of a number raised to the power of another number. | SELECT POWER(Salary, 2) FROM Employees; |
| SQRT() | Returns the square root of a number. | SELECT SQRT(Salary) FROM Employees; |
| RAND() | Generates a random number. | SELECT RAND() FROM Employees; |
| MOD() | Returns the remainder of a division operation. | SELECT MOD(Salary, 1000) FROM Employees; |
5. Date Functions
Date functions are employed to modify and process date and time information within SQL queries
| Function | Description | Example |
| GETDATE() | Returns the current date and time. | SELECT GETDATE(); (SQL Server) |
| CURRENT_TIMESTAMP | Similar to GETDATE(), returns the current date and time. | SELECT CURRENT_TIMESTAMP; |
| DATEADD() | Adds a specified interval to a date. | SELECT DATEADD(DAY, 5, HireDate) FROM Employees; (SQL Server) |
| DATEDIFF() | Returns the difference between two dates. | SELECT DATEDIFF(DAY, HireDate, GETDATE()) FROM Employees; |
| FORMAT() | Formats a date based on a specified format. | SELECT FORMAT(HireDate, ‘yyyy-MM-dd’) FROM Employees; |
| YEAR() | Extracts the year from a date. | SELECT YEAR(HireDate) FROM Employees; |
| MONTH() | Extracts the month from a date. | SELECT MONTH(HireDate) FROM Employees; |
| DAY() | Extracts the day from a date. | SELECT DAY(HireDate) FROM Employees; |
| DATEPART() | Returns a single part of a date, like year, month, or day. | SELECT DATEPART(YEAR, HireDate) FROM Employees; (SQL Server) |
Conclusion
Understanding and utilizing functions in SQL can greatly enhance your data manipulation and retrieval capabilities. From aggregate functions that summarize data to string functions that manage text, mastering these tools is crucial for effective database management.
Most Commented