Introduction
I will explain the system functions in this article and provide information about database objects. SQL server functions are used for objects in SQL Server databases. They can return only a single value and can only input parameters. System functions allow for the WHERE clause and anywhere expression. I have written this article focusing on beginners and students.
System Functions
A system function is used for operations and to return information about database objects in SQL Server. The user can never create them since they are pre-defined functions. The following system functions are commonly used in SQL (Aggregate, string, mathematical, cursor, date time).
![System function]()
Aggregate
This function analyzes a set of rows and returns turn a result based on groups of rows. It performs a calculation on a set of values and returns a single value. Aggregate functions ignore NULL values except for the COUNT function. It is also used, along with GROUPBY, for filtering queries using aggregate values. The following items are commonly used in aggregate functions (MIN, MAX, COUNT, SUM, AVG).
- COUNT: Returns the total number of records in a column or table.
- SUM: Returns the total number of a numeric column.
- AVG: Returns the average value of a numeric column
- MIN: Returns the smallest value of the selected column
- MAX: Returns the largest value of the selected column.
Syntax
SELECT MIN(COLUMN_NAME) FROM TABLE_NAME;
SELECT MAX(COLUMN_NAME) FROM TABLE_NAME;
SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;
SELECT SUM(COLUMN_NAME) FROM TABLE_NAME;
SELECT AVG(COLUMN_NAME) FROM TABLE_NAME;
String
This function is used to perform an operation on the input string and return an output string. The following items are commonly used in string functions (LEN, LTRIM, RTRIM, TRIM, UPPER, LOWER, SUBSTRING, REVERSE, REPLACE, CHARINDEX),
- LEN: Returns the length of a string.
- LTRIM: Removes leading spaces from a string
- RTRIM: Removes trailing spaces from a string
- TRIM: Remove spaces from a string.
- UPPER: Converts a string to upper-case.
- LOWER: Converts a string to lowercase.
- SUBSTRING: Extracts some characters from a string.
- REVERSE: Returns the reverse strings.
- REPLACE: Replaces all occurrences of a substring within a string with a new substring.
- CHARINDEX: Returns the position of a substring in a string.
Syntax
SELECT LEN(COLUMN_NAME) FROM TABLE_NAME
SELECT LTRIM(COLUMN_NAME) FROM TABLE_NAME
SELECT RTRIM(COLUMN_NAME) FROM TABLE_NAME
SELECT TRIM(COLUMN_NAME) FROM TABLE_NAME
SELECT LOWER(COLUMN_NAME) FROM TABLE_NAME
SELECT UPPER(COLUMN_NAME) FROM TABLE_NAME
SELECT SUBSTRING(COLUMN_NAME, 2, 2) FROM TABLE_NAME
SELECT REVERSE(COLUMN_NAME) FROM TABLE_NAME
SELECT REPLACE(COLUMN_NAME, 'X', 'Y') FROM TABLE_NAME
SELECT CHARINDEX('A', COLUMN_NAME) FROM TABLE_NAME
Mathematical Function
SQL Server provides a variety of mathematical functions. The mathematical function is for returning numeric expressions. The following items are commonly used in mathematical functions (CEILING, FLOOR, ROUND).
CEILING
Returns the smallest integer value that is >=X to it.
Example
X=12.320
SELECT CEILING(X) FROM TB_NAME
Output
X=13
FLOOR
Returns the largest integer value that is <=X to it.
Example
X=12.230
SELECT FLOOR(X) FROM TB_NAME
Output
X=12
ROUND
Rounds a number to a specified number of decimal places.
Example
X=12.230
SELECT ROUND(X,2) FROM TB_NAME
Output
X=12.23
DateTime
The DateTime function returns the DateTime. The following items are commonly used in DateTime functions (GETDATE, DATEADD, DATEDIFF, DATEPART, DATENAME, GETUTCDATE, SYSUTCDATETIME).
GETDATE
Return the current database system datetime.
SELECT GETDATE();
DATEADD
The datetime function adds a date and time or subtracts the date/time and returns the date.
SELECT DATEADD(MONTH, 10, GETDATE()); -- Add Month
SELECT DATEADD(DAY, 10, GETDATE()); -- Add day
SELECT DATEADD(YEAR, 10, GETDATE()); -- Add year
SELECT DATEADD(MONTH, -10, GETDATE()); -- Sub Month
SELECT DATEADD(DAY, -10, GETDATE()); -- Sub day
SELECT DATEADD(YEAR, -10, GETDATE()); -- Sub year
DATEDIFF
The datediff function displays the date and time between two dates.
SELECT DATEDIFF(MONTH,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w Month
SELECT DATEDIFF(DAY,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w day
SELECT DATEDIFF(YEAR,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w Year
DEPART
The datepart function returns a specified part of a date as an integer.
SELECT DATEPART(MONTH, GETDATE()); -- output is 11
SELECT DATEPART(DAY, GETDATE()); -- output is 12
SELECT DATEPART(YEAR, GETDATE()); -- output is 2019
DATENAME
DateName function returns a specified part of a date as a string.
SELECT DATENAME(MONTH,GETDATE());--output is november
SELECT DATENAME(DAY,GETDATE());--output is 12
SELECT DATENAME(YEAR,GETDATE())--output is 2019
GETUTCDATE
The getutcdate function returns a current database system UTC date and time.
SELECT GETUTCDATE()
SYSUTCDATETIME
The sysutcdatetime function returns the date and time of the SQL Server.
SELECT SYSUTCDATETIME()
Date Conversion
DateConversion returns the date and time in different formats. The following queries return the date and time in different formats in MS SQL Server.
SELECT CONVERT(NVARCHAR, GETDATE(), 100); -- output is Nov 14 2019 12:31AM
SELECT CONVERT(NVARCHAR, GETDATE(), 101); -- output is 11/14/2019
SELECT CONVERT(NVARCHAR, GETDATE(), 102); -- output is 2019.11.14
SELECT CONVERT(NVARCHAR, GETDATE(), 103); -- output is 14/11/2019
SELECT CONVERT(NVARCHAR, GETDATE(), 104); -- output is 14.11.2019
SELECT CONVERT(NVARCHAR, GETDATE(), 105); -- output is 14-11-2019
SELECT CONVERT(NVARCHAR, GETDATE(), 106); -- output is 14 Nov 2019
SELECT CONVERT(NVARCHAR, GETDATE(), 107); -- output is Nov 14, 2019
SELECT CONVERT(NVARCHAR, GETDATE(), 108); -- output is 00:31:46
Summary
In this article, you have had an overview of the system functions. I have written this article focusing on beginners and students.