Text copied!
SQL Built-In functions
In SQL, Function can be defined as a block/set of code that is used to perform a specific task or accomplish a particular purpose.
It can be reused through multiple projects which reduces code redundancy. 'Code redundancy' refers to the practice of writing the same code multiple times within a project.
Types of SQL functions :
1. User-defined function
2. System-defined function
1. User-defined function :
A user-defined function is a custom function created by a user. SQL Server allows you to create your own functions using the 'CREATE FUNCTION' statement that enables you to perform specific calculations.
2. System-defined function :
SQL Server provides extensive collection of built-in system functions is available that perform various tasks related to the database system. It is also famous as 'built-in functions'.
Calling function :
Calling function simply means executing or running it. To call a function in SQL Server, you can use the 'SELECT' statement in a SQL query.
Here's the syntax :
SELECT schema_name.function_name( arguments );
• Specify the schema name and function name after the 'SELECT' statement.
• Specify the argument in 'parenthesis ( )'. An argument is a value that is passed into a function.
Here's a list of popular SQL built-in functions :
1. String functions :
• CHARINDEX( )
• CONCAT( )
• LEN( )
• LOWER( )
• UPPER( )
• REPLACE( )
• SUBSTRING( )
• LEFT( )
• RIGHT( )
• TRIM( )
• RTRIM( )
• LTRIM( )
• STUFF( )
• PATINDEX( )
• QUOTENAME( )
• STR( )
• REVERSE( )
• CHAR( )
• NCHAR( )
• SPACE( )
2. Numeric functions :
• ROUND( )
• FLOOR( )
• CEILING( )
3. Date and Time functions :
• GETDATE( )
• DATEPART( )
• DATEADD( )
• DATEDIFF( )
• FORMAT( )
4. Aggregate/Window functions :
• SUM( )
• COUNT( )
• AVG( )
• MIN( )
• MAX( )
• ROW_NUMBER( )
• RANK( )
• DENSE_RANK( )
• LAG( )
• LEAD( )
5. Conversion functions :
• CAST( )
• CONVERT( )
• PARSE( )
• TRY_CAST( )
• TRY_CONVERT( )
• TRY_PARSE( )
• ISNULL( )
• COALESCE( )
• NULLIF( )
6. XML functions :
• XML PATH( )
• XML VALUE( )
• XML EXIST( )
• XML QUERY( )
• XML MODIFY( )
Here's an example of SQL built-in function :
[i] Let's assume you have a table called [Employees] table.
[ii] Let's assume you want to retrieve employee data from [Employees] table in order to identify the employees with the minimum and maximum salaries.
[iii] Run below SQL statement :
SELECT MIN(Salary) AS MIN_SALARY, MAX(Salary) AS MAX_SALARY
FROM [Employees];
[iv] Above statement uses 'MIN( )' and 'MAX( )' functions to retrieve the minimum and maximum salaries of employee's from [Employees] table.
[v] That's it! You have demonstrated the use of SQL 'built-in function'.