Summary -

In this topic, we described about the SCALAR Functions with detailed example.

SCALAR Functions returns single value for the specified input value. SCALAR Functions that are mainly used in SQL are listed as follows –

Function Description Syntax
UCASE( ) It returns the UPPER CASE value in result set for the specified value. UCASE(expression)
LCASE( ) It returns the LOWER CASE value in result set for the specified value. LCASE(expression)
MID( ) It returns the extracted characters from the specified string. It had three arguments like expression, start, number_of_char. We can specify from where we want to extract characters on start argument. We can specify how many characters we want to be extracted from string on number_of_char. MID(expression, start, number_of_char)
LEN( ) It returns the length of the specified expression in result set. LEN(expression)
NOW( ) It returns the current operating system DATE and TIME in result set. NOW( )
FORMAT() It returns the modified date in result by modifying specified DATE according to the required format. FORMAT(input_date, format )
ROUND( ) It round the numeric values to the nearest value to the specified numeric value. It is used on decimal values.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

SCALAR Functions that are mainly used in SQL are explained with examples as follows –

Function Example Output
UCASE() SELECT UCASE(emp_name) AS A FROM employee_details WHERE emp_id = 001; Now we can get output like as follows –
A
EMPLOYEE1
LCASE() SELECT UCASE(emp_name) AS A FROM employee_details WHERE emp_id = 001; Now we can get output like as follows –
A
EMPLOYEE1
MID() SELECT MID(emp_name, 3, 2) AS A FROM employee_details WHERE emp_id = 001; Now we can get output like as follows –
A
pl
LEN() SELECT LEN(emp_name) FROM AS A employee_details; Now we can get output like as follows –
A
9
NOW() SELECT NOW() AS A; Now we can get output like as follows –
A
2020-09-16 16:35:45.2589
FORMAT() SELECT FORMAT('2020-09-16 16:35:45.2589', 'YYYY-MM-DD') AS A; Now we can get output like as follows –
A
2020-09-15
ROUND() SELECT ROUND( 523698.93) AS A; Now we can get output like as follows –
A
523699

Creating SCALAR Function based on our requirement -

We can create SCALAR Functions to simply complex query by writing complicated calculations multiple times we can call created SCALAR Function in the query. SCALAR Function takes one or more arguments and returns a single value.

Syntax -

CREATE FUNCTION [schema_name.]function_name 
(parameter1, parameter2,….) 
RETURNS data_type AS
BEGIN   
	statements... 
	RETURN value 
END

schema_name is optional. It takes dbo by default.


Scenario – Creating SCALAR Function as per our requirement.

Requirement - Creating SCALAR Function Cubevalue as per our requirement. The query was as shown in below – 

CREATE FUNCTION Cubevalue(@a int)
RETURNS INT AS
BEGIN 
	RETURN @a* @a* @a
END

By executing above query we can create Cubevalue SCALAR Function.


Scenario -calling existing SCALAR Function.

Requirement -Calling existing SCALAR Function Cubevalue.The query was as follows -

SELECT dbo.Cubevalue(6) AS A;

By executing above query,we can get the cube value of 6 in result set. The output was as follows -

A
523699