SQL Functions Explained

In this article, I will explain the system functions and provide information about database objects. I have written this article focusing on beginners and students.

Introduction

 
In this article, I will explain the system functions 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. They can never be created by the user since they are pre-defined functions. The following system functions commonly used in SQL (Aggregate, string, mathematical, cursor, date time).
 
SQL Function Explained
 

Aggregate

 
This function is used to analyze a set of rows and return 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 total number of records in 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
  1. SELECT MIN(COLUMN_NAME) FROM TABLE_NAME;  
  2. SELECT MAX(COLUMN_NAME) FROM TABLE_NAME;  
  3. SELECT COUNT(COLUMN_NAME) FROM TABLE_NAME;  
  4. SELECT SUM(COLUMN_NAME) FROM TABLE_NAME;  
  5. 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 lower-case.
  • SUBSTRING - extracts some characters from a string.
  • REVERSE - returns the reverse strings.
  • REPLACE - Replaces all occurrence of a substring within a string with a new substring.
  • CHARINDEX - Returns the position of a substring in a string.
Syntax
  1. SELECT LEN(COLUMN_NAME) FROM TABLE_NAME  
  2. SELECT LTRIM(COLUMN_NAME) FROM TABLE_NAME  
  3. SELECT RTRIM(COLUMN_NAME) FROM TABLE_NAME  
  4. SELECT TRIM(COLUMN_NAME) FROM TABLE_NAME  
  5. SELECT LOWER(COLUMN_NAME) FROM TABLE_NAME  
  6. SELECT UPPER(COLUMN_NAME) FROM TABLE_NAME  
  7. SELECT SUBSTRING(COLUMN_NAME,2,2) FROM TABLE_NAME  
  8. SELECT REVERSE(COLUMN_NAME) FROM TABLE_NAME  
  9. SELECT REPLACE(COLUMN_NAME,'X','Y'FROM TABLE_NAME  
  10. SELECT CHARINDEX('A',COLUMN_NAME) FROM TABLE_NAME  

Mathematical Function

 
SQL Server provides a variety of mathematical function. The mathematical function is for returning numeric expressions. The following items commonly used in mathematical functions (CEILING, FLOOR, ROUND).
 

CEILING

 
Returns the smallest integer value that is >=X to it.
 
Example
 
 X=12.320
  1. SELECT CEILING(X) FROM TB_NAME  
Output
 
X=13
 

FLOOR

 
Returns the largest integer value that is <=X to it.
 
Example
 
X=12.230
  1. SELECT FLOOR(X) FROM TB_NAME  
Output
 
X=12
 

ROUND

 
Rounds a number to a specified number of decimal places.
 
Example
 
X=12.230
  1. SELECT ROUND(X,2) FROM TB_NAME  
Output
 
X=12.23
 

DateTime

 
The DateTime function is 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.
  1. SELECT GETDATE();  

DATEADD

 
Datetime function adds a date and time or subtract date/time and returns the date.
  1. SELECT DATEADD(MONTH,10,GETDATE());--Add Month  
  2. SELECT DATEADD(DAY,10,GETDATE());--Add day  
  3. SELECT DATEADD(YEAR,10,GETDATE());--Add year  
  4. SELECT DATEADD(MONTH,-10,GETDATE());--Sub Month  
  5. SELECT DATEADD(DAY,-10,GETDATE());--Sub day  
  6. SELECT DATEADD(YEAR,-10,GETDATE());--Sub year  

DATEDIFF

 
Datediff function displays the date and time between two dates.
  1. SELECT DATEDIFF(MONTH,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w Month  
  2. SELECT DATEDIFF(DAY,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w day  
  3. SELECT DATEDIFF(YEAR,DATEADD(MONTH,-10,GETDATE()),GETDATE());--Diff b/w Year  

DATEPART

 
Datepart function returns a specified part of a date as integer.
  1. SELECT DATEPART(MONTH,GETDATE())---output is 11  
  2. SELECT DATEPART(DAY,GETDATE())---output is 12  
  3. SELECT DATEPART(YEAR,GETDATE())—output is 2019  

DATENAME

 
DateName function returns a specified part of a date as a string.
  1. SELECT DATENAME(MONTH,GETDATE());--output is november  
  2. SELECT DATENAME(DAY,GETDATE());--output is 12  
  3. SELECT DATENAME(YEAR,GETDATE())--output is 2019  

GETUTCDATE

 
Getutcdate function is returning a current database system UTC date and time.
  1. SELECT GETUTCDATE()  

SYSUTCDATETIME

 
Sysutcdatetime function returns a date and time of the SQL Server.
  1. 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.
  1. SELECT CONVERT(NVARCHAR,GETDATE(),100)—output is Nov 14 2019 12:31AM  
  2. SELECT CONVERT(NVARCHAR,GETDATE(),101)--output is 11/14/2019  
  3. SELECT CONVERT(NVARCHAR,GETDATE(),102)--output is 2019.11.14  
  4. SELECT CONVERT(NVARCHAR,GETDATE(),103)--output is 14/11/2019  
  5. SELECT CONVERT(NVARCHAR,GETDATE(),104)--output is 14.11.2019  
  6. SELECT CONVERT(NVARCHAR,GETDATE(),105)--output is 14-11-2019  
  7. SELECT CONVERT(NVARCHAR,GETDATE(),106)--output is 14 Nov 2019  
  8. SELECT CONVERT(NVARCHAR,GETDATE(),107)--output is Nov 14, 2019  
  9. 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.