A function in SQL is a subprogram that is used to perform an action such as complex calculations and returns the result of the action as a value. It takes one or more arguments as input then processes the arguments and returns an output.
There are two types of SQL functions.
- AGGREGATE Functions.
- SCALAR Functions.
SCALAR Functions and AGGREGATE Functions both return a single value result.
AGGREGATE Functions operate on many records while SCALAR Functions operate on each record independently.
The functions which return only a single value from an input value are known as a scalar function. The Scalar function works on each record independently. SCALAR Functions are based on user input.
Scalar functions may take single or multiple arguments, but they always return a single-valued result which is mandatory. The returned values of the Scalar function can be of any data type.
Some SCALAR Functions in SQL
- UCASE
- LCASE
- MID
- ROUND
- LEN
- FORMAT
UCASE
SQL function UCASE() converts all the characters of a string to uppercase. It is a synonym for the upper case. It takes one argument and returns back only a single value.
It converts the value of a field to uppercase. It works on the values of all the data types and converts the string in the values to uppercase.
Syntax
SELECT UCASE(column_name) FROM table_name;
column_name is the field which we want in uppercase.
table_name is the name of the table to which the column belongs.For example:
SELECT UCASE(Emp_Name) FROM Employees;
This query returns the Emp_Name all in Caps from the Employees table.LCASE
SQL function LCASE() converts all the characters of a string to lowercase. It is a synonym for the lower case. It takes one argument and returns back only a single value.
It converts the value of a field to lowercase. It works on the values of all the data types and converts the string in the values to lowercase.
Syntax
SELECT UCASE(column_name) FROM table_name;
column_name is the field which we want in uppercase.
table_name is the name of the table to which the column belongs.For example:
SELECT UCASE(Emp_Name) FROM Employees;
This query returns the Emp_Name all in Caps from the Employees table.MID
SQL function MID() extracts texts from the value of a field. It takes one argument and works on each value independently.
It works on the values of all the data types.
Syntax
SELECT MID(column_name,start,length) FROM table_name;
column_name is the field which we want the function to retun.
table_name is the name of the table to which the column belongs.
start specifies the start position.(starts with 1)
length specifies the length of the text to be returned. it is optional. By default the length is set to the end position of the input.If the number of characters in the text of a field are less then the specified length, then space is appended at the end of the text.
For example:
SELECT MID(Emp_Name,1,5) FROM Employees;
This query returns the first 5 characters of the Emp_Name all the Employees.SELECT MID(Emp_Name,2) FROM Employees;
This query will return the Emp_Name starting from 2nd Character of all the Employees.
NEW
Android App Development Online Course by MindOrks
Start your career in Android Development. Learn by doing real projects.
ROUND
The ROUND() function is used to round a numeric field to the number of decimals specified.
It works on the values of numeric data types.
Syntax
SELECT ROUND(column_name,decimals) FROM table_name;
column_name is the field which we want the function to round up.
table_name is the name of the table to which the column belongs.
decimals represents the positions to which the numeric value should be rounded. It is optional. For example:
SELECT ROUND(Salary,0) FROM Employees;
This query returns the salary of all the Employees rounded up to 0 decimal places. E.g. Salary = 2000.50 Round(Salary) = 2000SELECT ROUND(Salary,2) FROM Employees;
This query returns the salary of all the Employees rounded up to 2decimal places. E.g. Salary = 2000.5064 Round(Salary) = 2000.51SELECT ROUND(Salary,-1) FROM Employees;
This query returns the salary of all the Employees rounded up to 2decimal places. E.g. Salary = 2008.5064 Round(Salary) = 2010LEN
The LEN() function is used to return the length of the value in the field. It counts the number of characters along with spaces and returns a single value integer.
It works on the values of all data types.
Syntax
SELECT LEN(column_name) FROM table_name;
column_name is the field which we want the function to return the length of.
table_name is the name of the table to which the column belongs.For example:
SELECT LEN(Emp_Name) FROM Employees;
This query returns the length of the name of all the Employees.SELECT LEN(Dept_Name) FROM Employees;
This query returns the length of the dept name of all the Employees.FORMAT
The FORMAT() function is used to format the contents of a field. It is used to specify how the contents of the field should be displayed or returned.
It works on the values of all data types.
Syntax
SELECT FORMAT(column_name,format) FROM table_name;
column_name is the field which we want the function to return the length of.
table_name is the name of the table to which the column belongs.
format specifies how the value should be displayed. it is written is single quotes.For example:
SELECT FORMAT(Hire_Date,'DD-MON-YYYY') FROM Employees;
This query returns the Hire_Date of all the Employees in DD-MON-YYYY format.SELECT FORMAT(Salary,'$ 999999.99') FROM Employees;
This query returns the salary of all the Employees in the given format.That's all about Scalar Functions. Hope you learned something new today.