Built-in scalar functions in SQL

A function can be defined as a set of instructions that performs a certain task automatically, and can be called within SQL instructions. The Built-in functions These are the system’s own functions, already defined by SQL Server.

Scalar functions operate on one value and return another. There are different types of scalar functions:

Configuration functions

They return information regarding the current configuration options. @@OPTIONS, @@LANGUAGE, @@SERVERNAME, or @@VERSION are a few examples of configuration functions.

Conversion functions

They are responsible for converting one data type to another. These types of functions include the following:

  • CAST(): ANSI-SQL standard function that converts a value from one data type to a different data type:
SELECT CAST(21.15 AS int);

The above execution will return 21. Since the value is converted to int, an integer numeric type, it does not allow decimals.

-CONVERT(): specific function of SQL Server that converts a value from one data type to a different data type:

SELECT CONVERT(int, 21.15);

The above execution will return 21. On a practical level, both functions share the same functionality.

When we convert date and time data types, it is possible to apply specific formats in the conversion through a new parameter:

DECLARE @date DATE = '2022-10-02';
Select CONVERT(VARCHAR, @date, 1) as [FORMAT MM/DD/YY]

The execution result will be: 10/02/22.

DECLARE @date DATE = '2022-10-02';
Select CONVERT(VARCHAR, @date, 2) as [FORMAT YY.MM.DD]

The execution result will be: 22.10.02.

To know the value of all the parameters and the format applicable to the date and time, access the Microsoft documentation.

  • PARSE(): function in charge of converting string type values to numeric or date/time data types:
SELECT PARSE ('September 27th 2022' AS DATE );

The above execution would return: 2022-27-09.

Cursor function

These functions return information about CURSORS like @@CURSOR_ROWS, @@FETCH_STATUS or CURSOR_STATUS.

Date and time functions

These functions operate on date and/or time values and return a numeric, string, or date and/or time value.

  • SYSDATETIME(): function that returns the date and time of the machine where SQL Server is running.
SELECT SYSDATETIME() AS sysdatetime;
  • GETDATE(): function that returns the current database system date and time, in the format YYYY-MM-DD hh:mm:ss.mmm.
SELECT GETDATE() AS getdate;
  • DAY(): function that returns the day portion of the date specified as an input parameter.
SELECT DAY('2022-09-27');

** The result of the execution will be: 27.

  • MONTH(): function that returns the month part of the specified date as an input parameter.
SELECT MONTH('2022-09-27');

The result of the execution will be: 09.

  • YEAR(): function that returns the year part of the specified date as an input parameter.
SELECT YEAR('2022-09-27');

The result of the execution will be: 2022.

  • ISDATE(): Function in charge of checking a date value. If the date is valid it will return 1, otherwise it will return 0.
SELECT ISDATE('2022-09-02 21:44:54');

The result of the execution will be: 1.

  • DATEDIFF(): Función encargada de devolver la diferencia entre dos fechas, siendo el primer parámetro, el intervalo entre ambas (YEAR, MONTH, DAY…)
SELECT DATEDIFF(YEAR, '2022-01-01', '2020-12-15');

The result of the execution will be -2.

Graph functions

Specific functions for the character conversion of the node identifiers and the graph edge. GRAPH_ID_FROM_EDGE_ID or NODE_ID_FROM_PARTS would be some examples of such functions.

JSON functions

Specific functions in charge of validating, consulting or modifying JSON data. JSON_VALUE, JSON_QUERY or JSON_MODIFY are some examples of this kind of function.

Logic functions

Functions responsible for performing logical operations.

  • CHOOSE(): function that returns an element that has been specified from a list of values through an index.
SELECT CHOOSE ( 2, 'Henry', 'John', 'Anna', 'Jessica' ) AS [name];

The result of the execution will return: John.

  • IIF: function responsible for validating an expression and returning a TRUE or FALSE boolean result.
DECLARE @a INT = 10, @b INT = 5;
SELECT [Result] = IIF( @a > @b, 'TRUE', 'FALSE' );

When the condition is met, the result of the execution will return: TRUE.

Mathematical functions

Perform mathematical calculations and return numeric values.

  • ABS(): function responsible for returning the absolute positive value according to the numerical value received as input parameter.
SELECT ABS(-10) AS [absolute];

The result of the execution will return: 10.

  • RAND(): function in charge of returning a pseudo-random float numeric value from 0 to 1 (both excluded).
SELECT RAND() AS random;

The result will be different in each execution.

  • ROUND(): function that returns a rounded numeric value to the specified precision.
SELECT ROUND(15.995, 2) AS rounding;

The result of the execution will be: 16.

Metadata function:

Return information about databases and the objects they contain. DB_NAME, COL_LENGTH or OBJECT_ID are some examples of metadata functions.

Security functions:

Inform about roles and users for security administration. PERMISSIONS, SESSION_USER, USER_ID are some examples of security functions.

Sytem functions:

Functions that return information about SQL Server instances.

System statistical functions:

Functions that return statistical information about the system. @@CONNECTIONS, @@TOTAL_ERRORS or @@TOTAL_WRITE are some examples of system statistics functions.

Test and image functions:

functions that receive columns of text or images as input parameters and return information about the value of the operations performed. TEXTPTR and TESTVALID are examples of text and image functions.

String functions:

They are functions that typically receive an input string value and return either a string value or a numeric value:

  • CHAR(): returns a one-byte character based on ASCII code.
SELECT CHAR(80) AS character1, CHAR(49) AS character2;

The execution result will return P for character1 and 1 for character2.

  • CONCAT(): concatenates two or more strings.
SELECT CONCAT ('Giselle is ', 12, ' years old') AS concatenation;

The result of the execution will be a single value: Giselle is 12 years old.

  • FORMAT: allows applying input masks to apply and return date/time values with the specified format. It also allows you to apply a specific culture.
DECLARE @date DATE =  '2022-09-27';
SELECT FORMAT (@date, 'dd/MM/yyyy');

The result of the execution will return the date in the format that we have specified: 27/09/2022.

DECLARE @date DATE =  '2022-09-27';
SELECT FORMAT( @date, 'd', 'en-US' ) AS [dateFormat]

The execution will return the date in the US format: 9/27/2022.

DECLARE @fecha DATE =  '2022-09-27';
SELECT FORMAT( @fecha, 'D', 'en-US' ) AS [dateFormat]

The result of the execution will return the date description in the US format: Tuesday, September 27, 2022.

  • REPLACE: replaces instances of a substring within a string, with a new substring.
SELECT REPLACE('WEB DB', 'DB', 'SQL');

The result of the execution will return: WEB SQL.

  • LOWER: function that converts characters in a string from uppercase to lowercase.
SELECT LOWER ('HELLO WORLD');

The result of the execution will return: hello world.

  • UPPER: converts characters in a string from lowercase to uppercase.
SELECT UPPER ('hello world');

The result of the execution will return: HELLO WORLD.

-STR(): returns a numeric data in a string.

SELECT STR(123.458, 5, 2);
  1. The first parameter refers to the numerical value to convert.
  2. The second parameter indicates the length of the string.
  3. The third parameter indicates the number of decimal places.

The result of the execution will be: 123.46.

  • SUBSTRING(): returns specific characters from a string:
SELECT SUBSTRING('Hello world', 7, 5);
  • The first parameter corresponds to the text string.
  • The second parameter indicates the starting position to retrieve the characters.
  • The third parameter indicates the length of characters to return starting from the initial position.

The result of the execution will be: world.