18
OctWebinar Alert : Mastering Manual and Automation Testing! - Reserve Your Free Seat Now
A function in SQL is a subroutine or a small program that can be used again and again throughout the database apps for data manipulation. Each database has a few in-built functions for the basic programming and you can define your own that are named as the user-defined functions. Customized functions in SQL are generally used to perform complex calculations and return the result as a value. The return value can be a single value or a result set. The objective of this SQL blog is to make you familiar with different types of SQL functions with examples and the basic syntax.
SQL Server Functions:
Types of SQL Server Functions:
And in system defined function we have 2 types of functions.
Scalar Functions:
Which operators on single value and returns single value, below is the list of some scale functions used in sql server.
Read: What is the Difference Between Control Flow & Data Flow in SSIS?
Scalar Function | Description |
round (9.56785) | This will round the give number to 3 places of decimal, 9.567 |
rand (10) | This will generate random numbers of 10 characters |
upper (‘sql’) | This will return upper case of given string, ‘SQL’ |
lower (‘SQL’) | This will return lower case of given string, ‘sql’ |
abs (-20.75) | This will return absolute number of a given number, 20.25 |
Convert (int, 20.56) | This will convert given float value to integer, 20 |
ltrim (' sql') | This will remove the spaces from left hand side, ‘sql’ |
rtrim (‘sql ‘) | This will remove the spaces from right hand side, ‘sql’ |
Substr () | This will extract characters from a text field |
ASCII (char_Exp) | This will return ASCII code of the given character expression |
Read More: Different Type of SQL Joins
Aggregate Functions:
Aggregates the values and return a single value, below is the list of some aggregate values in sql server.
Aggregate Function | Description |
Min () | This will return Minimum value |
Max() | This will return Maximum value |
Avg () | This will return Average value |
Count () | This will return number of counts |
Sum () | This will return total sum of numeric value |
These functions are created by user in the system database, and we have 3 types of user define functions:
Read More: Different Types of SQL Injection
Scalar Function:
Read: SAS Tutorial Guide for Beginners
It is a function that takes one or more values but returns a single value.
Example:
Learn SQL Server in the Easiest Way
Inline table-Valued function:
It returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.
Read: Most Frequently Asked RDBMS Interview Questions And Answers
Read More: SQL Server Interview Questions
Multi-Statement Table-Valued Function
Multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable, must be explicitly declared and defined whose value can be derived from a multiple sql statements.
SQL Server Training & Certification
The blog gives you a sound idea of functions and how they can be used within a database. Using functions is a good programming practice and if you want to know how to implement them for complex calculations then sign up for the SQL training now and start learning practical aspects of the SQL database programming in 2020 right away.
If you have the basic skills, then you can learn SQL by availing our self-learning courses with the best video content designed by our certified mentors.
Read: What is Data Mining SQL? Data Mining SQL Tutorial Guide for Beginner
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
MSBI Tutorial Guide for Beginners in 2024 14.6k
Comparative Study of SQL and NoSQL Databases 402.7k
What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key 489.2k
What is SQL Server Replication and How it Works? 763k
What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values) 327.2k
Receive Latest Materials and Offers on SQL Server Course
Interviews