Complete Function in SQL with Top 10 Interview Question

Function in SQL are required to convert the data from one type to another but also performing calculations on data. SQL has many built-in functions for performing these operations. So function in SQL topic, we will discuss more details about what are functions available in SQL. How to use these in our regular SQL queries.

What is the function in SQL?

SQL functions are simply sub-programs, which are commonly used and reused in SQL databases to process or manipulate data in SQL quries or stored procedures. SQL functions are small programs that may have zero or more input parameters but can return only one value.

Better to read the previous topic to get good knowledge about SQL.
JOIN in SQL
Wildcard Operators in SQL
Operator in Transact SQL
Data Query Language

Types of Function in SQL

SQL provides mainly two type of functions. Let’s see the types of functions.

Scalar Function

Scalar functions are based on user input, It will take one or more user input and will return a single value. UPPER(), LOWER(), LEN(), etc are example of the scalar function.

Aggregate Function

Aggregate functions are used to do operations from the values of the column and a single value is returned. SUM(), AVG(), MIN(), etc are the example of the aggregate function.

System Function

System functions in SQL Server that return objects, values, and settings in SQL Server. ISNULL(), ISNUMERIC(), etc are the example of the system function.

Table valued Function

A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.

Some SQL Function Examples

String Function

These are used to manipulate string in SQL. It has so many built in function Let’s see the below details.

UPPER() – This function converts to upper case letters.
LOWER() – This function converts to lower case letters.
LTRIM() – It removes leading space from a string.
RTRIM() – It removes trailing spaces from a string.
TRIM() – It removes both leading and trailing spaces from a string.
LEN() – It returns the length of the string.
SUBSTRING() – This function extracts some characters from a string. SUBSTRING(string, start, length).
STR() – It returns a number as a string.
REPLACE() – This function replaces all occurrences of a substring within a string, with a new substring. REPLACE(string, old_string, new_string)

Please see the example for use function inside query

SELECT UPPER('Welcome to SQL'), LOWER('Welcome to SQL'),
LTRIM('          Welcome to SQL'), RTRIM('Welcome to SQL    '), TRIM('  Welcome to SQL    '),
LEN('Welcome to SQL'),
SUBSTRING('Welcome to SQL', 1, 5),
STR(193),
REPLACE('SQL Tutorial', 'SQL', 'Python')

Math/Numeric Function in SQL

These function in SQL are take numeric value as parameter and retrun the respective value.

SUM() – It gives the result as the sum of a set of values.
AVG() – It returns the average value of an expression.
COUNT() – It returns the number of records returned by a select query.
MAX() – It returns the maximum value in a set of values.
MIN() – It returns the minimum value in a set of values.

ROUND() – It rounds a number to a specified number of decimal places.
CEILING() – It returns the smallest integer value that is >= a number.
FLOOR() – It returns the largest integer value that is <= to a number.
ABS() – It returns the absolute value of a number.
RAND() – It returns a random number.
SQUARE() – It returns the square of a number.
SQRT() – It returns the square root of a number.
POWER() – It returns the value of a number raised to the power of another number.

SELECT SUM(10,30), AVG(29,41), MAX(23,90),
SQRT(90), POWER(3,2)

Date Function in SQL

GETDATE() – It returns the current date and time

SELECT GETDATE()

DATEADD() – This function adds a time/date interval to a date and then returns the date.

Syntax:-
DATEADD(interval, number, date)
Interval parameter expecting the value as below
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear, dy, y = Day of the year
day, dd, d = Day
week, ww, wk = Week
weekday, dw, w = Weekday
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond
Number – Pass any numeric value. The number of intervals to add to the date
Date – Which date will be modified

SELECT DATEADD(mm, 1, '2021/06/16') 

ISDATE() – This function in sql checks the value and returns 1 if it is a valid date, otherwise 0

SELECT ISDATE('2021/06/16')

DATEDIFF() – This function in SQL returns the difference between two date values, based on the interval specified.

SELECT DATEDIFF(month, '2021/06/16', '2021/09/29');

Useful Function in SQL

ISNUMERIC() – This function returns 1 if the expression is a valid number. Otherwise, it returns 0.

SELECT ISNUMERIC('Welcome to SQL Tutorials');

CONVERT and CAST function in SQL

These function in SQL are used for convert an expression of one data type to another.

Syntax
CAST ( expression AS data_type [ ( length ) ] )

CONVERT (datatype [ ( length ) ], expression [ , style ])

Datatype: This parameter defines the target data type which is to be converted.
Length: This is an optional parameter that specifies the target data type length.
Expression: This parameter specifies the value which we want to convert to another data type.
Style: This is an integer parameter that specifies the output style of the converted value.

Please see the below example for convertation function in SQL.

Convert Float to Integer

SELECT CONVERT(int, 129.45) AS ConvertedValue
SELECT CAST(129.45 as int)  AS CastedValue

Convert Float to Varchar

SELECT CONVERT(varchar, 129.45) AS ConvertedValue
SELECT CAST(129.45 as varchar)  AS CastedValue

Convert Date/Time to Varchar

SELECT CONVERT(Varchar(50),GETDATE(),103) AS BritishStyleDate
SELECT CAST(GETDATE() AS DATETIME) AS CastedDate

TRY_CONVERT function in SQL

The SQL TRY_CONVERT function is an advanced form of the SQL CONVERT function.
It is possible that we can experience errors in the data conversion process with the SQL COVERT operation due to invalid data. However, the SQL TRY_CONVERT function allows us to avoid these types of errors.
The SQL TRY_CONVERT function returns a NULL value if the data conversion generates an error.

SELECT CONVERT(INT,'SQL Tutorial') AS ConvertedValue -- Throw the error
SELECT TRY_CONVERT(INT,'SQL Tutorial') AS ConvertFunc -- It will return NULL because string can't be convert to integer.

User Define Function

It accept inputs, perform actions using those inputs, and return the output. The exact syntax for doing this can vary based on your specific database software, but SQL UDFs typically have at least four elements:

• A name, which is used to invoke the function
• Arguments, the inputs a function takes
• An output or return statement
• The function body itself (i.e., the code that the database software will execute using the provided inputs)

We will see the syntax to create, modify and delete function in SQL Server.

Syntax Create User Define Function

CREATE FUNCTION function_name (parameters)
RETURNS data_type AS
BEGIN
–SQL statements
RETURN value
END;

Modify User Define Function

ALTER FUNCTION function_name (parameters)
RETURNS data_type AS
BEGIN
–SQL statements
RETURN value
END;

Delete User Define Function

DROP FUNCTION function_name;

We will create a function in SQL Server to get the total number of employee based input department id. Let’s see the below example.

Example:-

CREATE FUNCTION getTotalEmployeebyDepartment(@DEPTID INT)
RETURNS INT AS
BEGIN
	DECLARE @TOTALEMPLOYEE INT
	SELECT @TOTALEMPLOYEE= COUNT(*) FROM EMPLOYEE 
	WHERE DEPTID=DEPTID
	RETURN @TOTALEMPLOYEE
END;

Call Function in SQL

We can call the functions the same as the built-in function in SQL Server.

Syntax to Call Function

Select * schema_name.function_name(parameter)

Select * from schema_name.function_name(parameter)

Select * from table_name
Where schema_name.function_name(parameter) > 10

For example, we can call the above getTotalEmployeebyDepartment function as below

select dbo.getTotalEmployeebyDepartment(1) 'Total Employee By Department ID'

Now, We will the above function from SQL Server by using drop commands. Please follow the below command to remove Function.

drop function dbo.getTotalEmployeebyDepartment

Reference Link
Function in SQL Server

Related posts:

Data Query Language(DQL) OR SELECT Statement in SQL JOIN in SQL with Top 10 Example Operator in Transact SQL Group By Clause