35+ SQL Functions Explained in Detail [Practical Examples]

Overview of SQL Functions

SQL provides much in-build function to perform operation of table data, these functions can be with-in SQL statements or queries, and can also be used within the programming environment provided by the SQL Server (Transact-SQL) database, such as stored procedures, functions, triggers, etc.

SQL Built-In functions are generally used to perform string concatenations, mathematical calculations etc.

SQL functions are categorized into the following two categories:

1. SQL Built-In Functions
2. SQL User Defined Function

 

SQL Built-In Functions

SQL provides many built-in functions for performing processing on string or numeric data, the following are the list of categories of Built-In functions based on the type of data used in operation

1. Aggregate Functions
2. Scalar functions

 

Aggregate Functions

Aggregate functions operates on set of numeric values and return a single value, SQL Aggregate function are used as the select list of SQL SELECT statement and also  can be used in combination with the GROUP BY clause to calculate the aggregation on categories of rows

 

List of Built-In SQL Aggregate Functions

1. AVG
2. CHECKSUM_AGG
3. COUNT
4. COUNT_BIG
5. GROUPING
6. GROUPING_ID
7. MAX
8. MIN
9. STDEV
10. STDEVP
11. STRING_AGG
12. SUM
13. VAR
14. VARP

 

Examples of SQL Aggregate Functions

Consider School result management system to perform practical examples on SQL functions
Student Table

Result Table

Subject Table

 

SQL AVG() Function with Example

SQL AVG() function is used to  calculate the average value of given numeric values

Example 1: Write SQL query to calculate average obtain marks of each semester student

SELECT examname, AVG(obtainmark) AS 'Avarange marks'
FROM tblresult
GROUP BY examname

• In the above query, SQL AVG() function is applied on obtain marks column of result table to calculate average value of obtain mark values
• SQL Group By clause is used make group of records based on examname column value

OUTPUT:

 

SQL  CHECKSUM_AGG Function with Example

The CHECKSUM_AGG() function is used to calculates a checksum value based on a group of records

Example 2: Write SQL query to calculate checksum value of semester 3 student’s obtain mark

SELECT CHECKSUM_AGG(obtainmark) AS 'Checksum obtain mark'
FROM  tblresult
WHERE (examname = 'sem3')

• In the above query, SQL CHECKSUM_AGG function is used with obtainmark column to calculate checksum value
• SQL where clause is used to conditionally retrieve sem 3 records from result table

OUTPUT:

 

SQL COUNT() Function with Example

SQL COUNT() function is used to count the total number of records which matches with given condition

Example 3: Write SQL query to count total number of students citywise

SELECT  city, COUNT(*) AS 'Total student'
FROM tblstudent
GROUP BY city

• In the above query, SQL COUNT() Aggregate function is used to count total number of students lived in each city
• SQL Group By clause is used to make a group of records based on city name

OUTPUT:

 

SQL COUNT_BIG() Function with Example

The COUNT_BIG()  function is used to count  the number of records including records with NULL values

Example 4 : Write SQL query to count total number of records in result table

SELECT COUNT_BIG(obtainmark) AS 'Total Records'
FROM  tblresult

In the above query, SQL COUNT_BIG() function is used to count total records in result table

OUTPUT:

 

SQL GROUPING () Function with Example

SQL GROUPING function is used to identify whether a specified column expression in a GROUP BY list is aggregated or not

Example 5: Write SQL query to check grouping is performed on city column or on class ID column

SELECT city, SUM(student_id) AS 'Total Students', GROUPING(city) AS 'Grouping'
FROM  tblstudent
GROUP BY city WITH ROLLUP

• In the above query, SQL GROUPING function is applied on city column to check whether group has performed on city column with GROUP BY clause or not
• The result set shows NULL value in the last summery record added by ROLLUP operation
• The summery record shows total number of student of all groups and it indicates 1 in grouping result

OUTPUT:

 

SQL GROUPING_ID() Function with Example

SQL GROUPING_ID() function is use to concatenates the output of the GROUPING functions applied to all the columns specified in the GROUP BY clause

GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified

Example 6: Write SQL query to concatenate grouping value of two columns city and class_id

SELECT city, GROUPING_ID(city,class_id) AS 'Grouping'
FROM  tblstudent
GROUP BY city WITH ROLLUP

• In the above query , SQL GROUPING_ID function is used to concate grouping output of two columns city and class_id
• The result set shows NULL value in the last summery record added by ROLLUP operation
• The summery record shows total number of student of all groups and it indicates 1 in grouping result

OUTPUT:

 

SQL MAX() Function with Example

SQL MAX() function is used to find Maximum  value from the list of values

Example 7: Write SQL query to find maximum value of obtain mark for sem3 students

SELECT MAX(obtainmark) AS 'Maximum marks sem3'
FROM  tblresult
WHERE (examname = 'sem3')

• In the above query, SQL MAX () function is applied on obtain mark column to find maximum mark from all students
• SQL Where clause  is used  to conditionally retrieve records of sem3 students

OUTPUT:

 

SQL MIN() Function with Example

SQL MIN() function is used to find Minimum value from the list of values

Example 8: Write SQL query to find minimum value of obtain mark for sem3 students

SELECT MIN(obtainmark) AS 'Minimum marks sem3'
FROM tblresult
WHERE (examname = 'sem3')

• In the above query, SQL MIN () function is applied on obtain mark column to find minimum mark from all students
• SQL Where clause  is used  to conditionally retrieve records of sem3 students

OUTPUT:

 

SQL STDEV() Function with Example

SQL STDEV() function is used to calculate the Standard Deviation of total records retrieved by the SQL SELECT Statement

Example 9: Write SQL query to calculate standard deviation of obtain marks of semester 3 students

SELECT STDEV(obtainmark) AS 'Standard Deviation'
FROM  tblresult
WHERE (examname = 'sem3')

In the above query, SQL STDEV() function is applied on obtainmarks column to calculate standard deviation of obtain marks value of semester 3 student

OUTPUT:

 

SQL STDEVP() Function with Example

SQL STDEVP() function is used to calculates the Statistical Standard Deviation for the population of total records selected by the SELECT Statement

Example 10: Write SQL query to calculate statistical standard deviation of obtain marks of all semester 3 students

SELECT STDEVP(obtainmark) AS 'Statistical Standard Deviation '
FROM tblresult
WHERE (examname = 'sem3')

In the above query, SQL STDEVP() function is applied on obtainmarks column to calculate statistical standard deviation of obtain marks value of semester 3 student

OUTPUT:

 

SQL STRING_AGG() Function with Example

SQL STRING_AGG() function is used to concatenates the string expressions retrieved as the select list with SQL select statement , and places a specified separator in-between them

Example 11: Write SQL query to concatenate student name value of all records separated by comma(,)

SELECT STRING_AGG(studentname,' , ') AS 'Student Name '
FROM  tblstudent

• In the above query, SQL STRING_AGG function is used with two arguments , studentname and ‘,’
• The first argument specifies column name, and second argument separator symbol in the single quotation

OUTPUT:

 

SQL SUM () Function with Example

SQL SUM() function is used to calculate the  total of given numeric values

Example 12: Write SQL query to make total of all obtain marks of each semester

SELECT examname, SUM(obtainmark) AS 'Total obtain marks'
FROM tblresult
GROUP BY examname

• In the above query SQL SUM() is used with SQL select statement to make total of obtain marks
• SQL Group by clause is used to make group of records based on examname

OUTPUT:

 

SQL VAR() Function with Example

SQL VAR() is used to calculate the statistical variance of sample records selected by the SELECT Statement

SQL VAR() function can only be used with numeric columns , Null values are ignored

Example 13: Write SQL query to find variance value of obtainmark column semester wise

SELECT examname, VAR(obtainmark) AS 'Variance value'
FROM tblresult
GROUP BY examname

In the above query, SQL VAR() function is applied on obtainmark column to calculate variance value of each semester student marks based on sample obtain mark

OUTPUT:

 

SQL VARP() Function with Example

SQL VARP() function is used to calculates the statistical variance for the population of total rows selected by the SQL SELECT Statement

SQL VARP() function can be used with numeric columns only,Null values are ignored

 

Difference between VAR() and VARP() Functions

VarP() evaluates a population, whereas the Var() function evaluates a population sample

Example 14: Write SQL query to find variance of total evaluated obtain marks of all students semsterwise

SELECT examname, VARP(obtainmark) AS 'Variance value'
FROM tblresult
GROUP BY examname

• In the above SQL query , SQL VARP() function is applied on obtainmark column with grouping of semester
• SQL VARP() function calculate variance of total obtain marks of each semester

OUTPUT:

 

SQL Scalar functions

Scalar functions operate on a single value and then return a single value, Scalar functions can be used as an expression or as select list of SQL SELECT statement

 

List of Build-in Scalar Functions

1. STRING FUNCTIONS

• RIGHT
• LEFT
• LEN
• RTRIM
• LTRIM
• REPLACE
• REVERSE
• SUBSTRING
• LOWER
• UPPER

2. DATE FUNCTIONS

• DATEADD
• DATEDIFF
• DAY
• MONTH
• YEAR
• GETDATE

3. NUMERIC FUNCTIONS

• FLOOR
• CEILING
• ROUND

4. CONVERSION FUNCTIONS

• CAST
• CONVERT

5. NULL-RELATED FUNCTIONS

• ISNULL

 

SQL STRING FUNCTIONS Examples

SQL RIGHT() FUNCTION WITH Example

SQL RIGHT() function returns specified number of characters from the right side of given string

Example 15: Write SQL query to retrieve last 8 characters from given string ‘SQL Tutorial'

SELECT RIGHT('SQL Tutorial', 8) AS 'RIGHT FUNCTION'

In the above query, SQL RIGHT() function is used with two arguments, the first argument specifies  the string from which the characters will retrieve and second argument specifies the number of characters to be retrieved

OUTPUT:

 

SQL LEFT() FUNCTION WITH Example

SQL LEFT() function returns specified number of characters from left side of given string

Example 16: Write SQL query to retrieve first 3 characters from given string 'SQL Tutorial'

SELECT LEFT('SQL Tutorial', 3) AS 'LEFT FUNCTION'

In the above query, SQL LEFT() function is used with two arguments, the first argument specifies  the string from which the characters will retrieve and second argument specifies the number of characters to be retrieved

OUTPUT:

 

SQL LEN() FUNCTION WITH Example

SQL LEN() function is used to count number of characters in the given string

Example 17: Write SQL query to count number of characters of a given string  ‘SQL Tutorial'

SELECT LEN('SQL Tutorial') AS 'Length'

In the above query, SQL LEN() function specified with one argument of a string ‘SQL Tutorial’ to count number of characters in a string

OUTPUT:

 

SQL RTRIM() FUNCTION WITH Example

SQL RTRIM() function is used to remove all trailing blanks from the right side of given string

Example 18: Write SQL query to remove all trailing blank space from right side of given string ‘  SQL Tutorial    ’

SELECT RTRIM('   SQL Tutorial    ') AS 'RTRIM'

• In the above query, SQL RTRIM() function specified with one argument of string ‘ SQL Tutorial   ‘ with the trailing blank space in the right and left side of a string
• SQL RTRIM() function will remove the space from the right side of a string

OUTPUT:

 

SQL LTRIM() FUNCTION WITH Example

SQL LTRIM() function is used to remove all trailing blanks from the left side of given string

Example 19: Write SQL query to remove all trailing blank space from left side of given string ‘  SQL Tutorial    ’

SELECT LTRIM('   SQL Tutorial    ') AS 'LTRIM'

• In the above query, SQL LTRIM() function specified with one argument of string ‘ SQL Tutorial   ‘ with the trailing blank space in the right and left side of a string
• SQL LTRIM() function will remove the space from the left side of a string

OUTPUT:

 

SQL REPLACE() FUNCTION WITH Example

SQL REPLACE() function is used to replace all occurrence of specified character in a given string with new character

Example 20: Write SQL query to replace character ‘T’ in the given string with new character ’$’

SELECT REPLACE('SQL Tutorial', 't', '$') AS 'REPLACE'

• In the above query, SQL REPLACE() function specified used with three arguments
• The first argument is the string in which we want to perform replacement
• The second argument is the character to be replaced
• The third argument is the new character with which character will be replaced

OUTPUT:

 

SQL REVERSE() FUNCTION WITH Example

SQL REVERSE() function is used to reverse the specified string

Example 21: Write SQL query to reverse the given string ‘SQL Tutorial’

SELECT REVERSE('SQL Tutorial') AS 'REVERSE' In the above query, SQL REVERSE () function specified with one argument of string to be revered

OUTPUT:

 

SQL SUBSTRING() FUNCTION WITH Example

SQL SUBSTRING() function is used to extract part of string

Example 22: Write SQL query to extract string ‘Tutorial’ from the given string ‘SQL Tutorial Videos’

SELECT SUBSTRING('SQL Tutorial Videos', 5, 8) AS 'SUBSTRING'

• In the above query, SQL SUBSTRING () function specified with three arguments to extract part of inputted string ‘'SQL Tutorial Videos'
• The First argument specifies the input string from which a part is going to be extracted
• The Second argument specified the starting index
• The Third argument specified number of characters

OUTPUT:

 

SQL LOWER() FUNCTION WITH Example

SQL LOWER() function is used to convert all upper case character to lower case characters

Example 23: Write SQL query to convert all upper-case letters in a given string to lower-case letters

SELECT LOWER('SQL Tutorial') AS 'LOWER()' In the above query, SQL LOWER () function is applied on a string” SQL Tutorial” to convert all letters of given string into lower case

OUTPUT:

 

SQL UPPER() FUNCTION WITH Example

SQL UPPER() function is used to convert all lower case characters to upper case characters

Example 24: Write SQL query to convert all lower-case letters in a given string to upper-case letters

SELECT UPPER('SQL Tutorial') AS 'UPPER()'

In the above query, SQL UPPER () function is applied on a string” SQL Tutorial” to convert all letters of given string into upper case

OUTPUT:

 

SQL DATE FUNCTIONS with Examples

SQL DATEADD() FUNCTION with Example

SQL DATEADD() function is used to add specified number interval to the specified date part of a given date

Example 25: Write SQL query to add two months to the given date '02-02-1990'

SELECT DATEADD(mm, 2, '1990-02-02') AS [DATEADD()]

• In the above query, SQL DATEADD() function specifies with 3 arguments to add 2 months to given date
• The first argument specifies the part of date ‘mm’ represents month, ‘dd’ represents date and ‘yy’ represents year
• The second argument specifies the numeric value to be added to the given date value
• The third argument is date in which months to be added

OUTPUT:

 

SQL DATEDIFF() FUNCTION with Example

SQL DATEDIFF() function is used to find the difference between two given dates based on specified datepart

Example 26: Write SQL query to fund month difference between two date ’01-01-1990’ & ’03-03-1990’

SELECT DATEDIFF(mm, '1990-01-02', '1990-03-03') AS [DATEDIFF()]

• In the above query, SQL DATEDIFF() function specifies with 3 arguments to find the difference between two given dates
• The first argument specifies the part of date ‘mm’ represents month, ‘dd’ represents date and ‘yy’ represents year
• The second argument specifies the first date
• The third argument specifies the second date

OUTPUT:

 

SQL DAY() FUNCTION with Example

SQL DAY() function is used to retrieve the day of a month in integer number from the given date value

Example 27: Write SQL query to retrieve day number from the date ’02-01-1990’

SELECT DAY('1990-01-02') AS [DAY()] In the above query, SQL DAY() function specified with one argument of date to retrieve day of a month in integer number

OUTPUT:

 

SQL MONTH() FUNCTION with Example

SQL MONTH() function is used to retrieve month from given date value in an integer number

Example 28: Write SQL query to retrieve month from the date ’02-01-1990’

SELECT MONTH('1990-01-02') AS [MONTH()] In the above query, SQL MONTH() function specified with one argument of date to retrieve month in integer number

OUTPUT:

 

SQL YEAR() FUNCTION with Example

SQL YEAR() function is used to retrieve year from given date value

Example 29: Write SQL query to retrieve year from the date ’02-01-1990’

SELECT YEAR('1990-01-02') AS [YEAR()]

In the above query, SQL YEAR() function specified with one date argument to retrieve year from given date value

OUTPUT:

 

SQL GETDATE() FUNCTION with Example

SQL GETDATE() function is used to find the current database system date

Example 30 : Write SQL query to retrieve current date of system

SELECT GETDATE() AS [CURRENT DATE]

In the above query, SQL GETDATE() function is used with SQL select statement to retrieve current date

OUTPUT:

 

SQL NUMERIC FUNCTIONS

SQL FLOOR() FUNCTION with Example

SQL FLOOR() function is used to find largest integer which is less than or equal to given numerical value

Example 31: Write SQL query to find integer number of 29.90

SELECT FLOOR(29.90) AS 'FLOOR()'

In the above query, SQL FLOOR() function specified with one argument of floating number 29.90 to find integer floor value

OUTPUT:

 

SQL CEILING() FUNCTION with Example

SQL CEILING() function is used to find smallest integer which is greater than or equal to given numerical value

Example 32: Write SQL query to find integer number of 29.90

SELECT CEILING(29.90) AS CEILING()'

In the above query, SQL CEILING() function specified with one argument of floating number 29.90 to find integer floor value

OUTPUT:

 

SQL ROUND() FUNCTION with Example

SQL ROUND() function is used to find roundup value of given numeric value

EXAMPLE 33:  Write SQL query to find roundup value of given numeric value 29.90

SELECT ROUND(29.90, 0) AS 'ROUND()'

• In the above query, SQL ROUND() function specified with two argument
• The first argument is a numeric value to be roundup
• The second argument is a precision digit

OUTPUT:

 

SQL CONVERSION FUNCTIONS

SQL CAST() FUNCTION with Example

SQL CAST() function is used to convert given value into another datatype

Example 34: Write SQL query to cast given date datatype value ’01-02-1990’ to varchar datatype

SELECT CAST('1990-02-01' AS VARCHAR) AS 'VARCHAR Datatype'

OUTPUT:

 

SQL CONVERT() FUNCTION with Example

SQL CONVERT() function is used to convert given value to another datatype

Example 35: Write SQL query to convert given date datatype value ’01-02-1990’ to varchar datatype

SELECT CONVERT(VARCHAR, '1990-02-01') AS 'VARCHAR Datatype'

OUTPUT:

 

SQL NULL-Related Functions

SQL ISNULL() Function with Example

SQL ISNULL() function is used to replace NULL value with specified value

Example 36: Write SQL query to replace NULL with value ‘ISNULL Function’

SELECT ISNULL(NULL, 'ISNULL Function') AS 'ISNULL Function'

OUTPUT:

 

SQL User Defined Function

SQL Server allows to create user-defined function using CREATE FUNCTION statement, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value

There are two main types of user-defined functions in SQL based on the data they return:

1.User-defined Scalar functions

These types of functions return a single value like float, int, varchar, datetime and so on..

2. Table-Valued functions

These functions return tables

 

Syntax to create User-define Scalar function

CREATE FUNCTION scalar_function_name
    (
	@parameter1 AS datatype
	@parameter2  AS datatype 
    )
    RETURNS <return datatype>
    AS
    BEGIN
	RETURN <return value/expression >
END;

 

Example of User-define Scalar function

Example 37: Create User-define Scalar function to calculate percentage of all students

CREATE FUNCTION percentage_student
(
    @totalmarks int,
    @obtainmark  int
)
returns int
as
begin
  return round(@obtainmark *100/@totalmarks,0) 
end

• In the above User-defined SQL function, CREATE FUNCTION statement is used to create user-defined scalar function as percentage_student
• The function contains two parameters @totalmarks and @obtainmarks of int datatype
• Return type of the function is int
• The function will return percentage of all students based on the records of result table

OUTPUT:

To see the result of above created function we need to call this function within SQL SELECT statement

SELECT  student_id AS 'Student ID', examname AS Semester, dbo.percentage_student(obtainmark, totalmarks) AS Percentage
FROM  tblresult

 

Syntax to create User-define Table-valued function

CREATE FUNCTION function_name
 (
    @parameter1  dataype,
)
RETURNS TABLE
AS
RETURN
    SELECT column1,column2…
    FROM
        table_name
    WHERE
        Condition

 

Example of User-define Table-valued function

Example 38: Create user-defined function to generate table contains result of semester 3 students

CREATE FUNCTION result_semester3
 (
  @semester varchar
    )
RETURNS TABLE
AS
RETURN
    SELECT examname,totalmarks,obtainmark,pecentage FROM tblresult    WHERE examname=@semester

• In the above User-defined SQL function, CREATE FUNCTION statement is used to create user-defined scalar function as result_semester3
• The function contains one parameters @semester of varchar datatype
• The function will return a table as result set

OUTPUT:

To see the result of above created function we need to call this function with SQL SELECT statement

SELECT  examname, totalmarks, obtainmark, pecentage
FROM   dbo.result_semester3('sem3') AS Result

 

Summary

In this article of SQL function, we have covered overview of SQL functions with the types of SQL function: Built-in SQL functions and User-defined functions, In the built-in type SQL function we have explained Aggregate functions and Scalar functions with practical examples, also we have explained SQL User-defined function with two types: Scalar User-defined function and Table-valued User-defined function with practical examples

 

References

SQL Date functions
SQL Aggregate functions

 

Read More

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver15

 

Related Keywords: sql create function, sql function example, sql function syntax, oracle sql functions, sql functions list pdf, function in sql server, sql functions w3schools, user defined functions in sql