SQL Server > Transact-SQL > Language Elements > System Functions

System Functions

System functions return a value, which can be a scalar (single value), the name of a data type, or a set of values returned in a table.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single scalar value.

Scalar Functions

Scalar functions return a single value.

String Functions

Date and Time Functions

Numeric Functions

System Information Functions

AVG

Returns the average value of an expression.

AVG ( [ ALL | DISTINCT ] expression )

Example:

SELECT AVG(SalesYTD) FROM Sales.SalesPerson;

COUNT

Counts the number of items in a group.

COUNT ( { [ ALL ] expression | * | DISTINCT | expression } )

Example:

SELECT COUNT(*) FROM Production.Product;

MAX

Returns the maximum value of an expression.

MAX ( [ ALL | DISTINCT ] expression )

Example:

SELECT MAX(ListPrice) FROM Production.Product;

MIN

Returns the minimum value of an expression.

MIN ( [ ALL | DISTINCT ] expression )

Example:

SELECT MIN(StandardCost) FROM Production.Product;

STDEV

Returns the statistical standard deviation of all values in the specified expression.

STDEV ( [ ALL | DISTINCT ] expression )

Example:

SELECT STDEV(OrderQty) FROM Sales.SalesOrderDetail;

STDEVp

Returns the statistical P-value of all values in the specified expression.

STDEVp ( [ ALL | DISTINCT ] expression )

Example:

SELECT STDEVp(UnitPrice) FROM Production.ProductListPriceHistory;

VAR

Returns the statistical variance of all values in the specified expression.

VAR ( [ ALL | DISTINCT ] expression )

Example:

SELECT VAR(UnitPriceDiscount) FROM Sales.SalesOrderDetail;

VARp

Returns the statistical P-value of all values in the specified expression.

VARp ( [ ALL | DISTINCT ] expression )

Example:

SELECT VARp(TerritoryID) FROM Sales.SalesPerson;

LEN

Returns the number of characters in a string expression, excluding trailing blanks.

LEN ( string_expression )

Example:

SELECT LEN('Hello World  ');

LOWER

Returns a character string after converting uppercase characters to lowercase characters.

LOWER ( character_expression )

Example:

SELECT LOWER('Microsoft SQL Server');

UPPER

Returns a character string after converting lowercase characters to uppercase characters.

UPPER ( character_expression )

Example:

SELECT UPPER('Microsoft SQL Server');

SUBSTRING

Returns a part of a character string, binary string, text or image expression.

SUBSTRING ( expression , start , length )

Example:

SELECT SUBSTRING('Computer', 1, 4);

CONCAT

Concatenates two or more string expressions. CONCAT is a shorthand for the + operator when used with two arguments. It also accepts more than two arguments, unlike the + operator.

CONCAT ( string_expression1, string_expression2 [, string_expressionN ]... )

Example:

SELECT CONCAT('Hello', ' ', 'World');

REPLACE

Returns a character string after replacing all occurrences of a specified string with another string.

REPLACE ( string_expression , string_pattern , string_replacement )

Example:

SELECT REPLACE('It is a nice day', 'nice', 'beautiful');

TRIM

Removes leading and trailing spaces or specified characters from a string.

TRIM ( [ [ LEADING | TRAILING | BOTH ] [ character [ FROM ] ] string_expression )

Example:

SELECT TRIM('   Hello World   ');

GETDATE

Returns the current database system timestamp as a datetime value.

GETDATE()

Example:

SELECT GETDATE();

DATEADD

Returns a specified date with a specified time interval added.

DATEADD ( datepart , number , date )

Example:

SELECT DATEADD(day, 10, '2023-01-01');

DATEDIFF

Returns the difference between two dates, expressed in the specified datepart.

DATEDIFF ( datepart , startdate , enddate )

Example:

SELECT DATEDIFF(day, '2023-01-01', '2023-01-10');

DATENAME

Returns a specified part of a specified date as a character string.

DATENAME ( datepart , date )

Example:

SELECT DATENAME(weekday, '2023-01-01');

DATEPART

Returns a specified part of a specified date as an integer.

DATEPART ( datepart , date )

Example:

SELECT DATEPART(month, '2023-01-01');

ABS

Returns the absolute value of a numeric expression.

ABS ( numeric_expression )

Example:

SELECT ABS(-10);

CEILING

Returns the smallest integer greater than or equal to the specified numeric expression.

CEILING ( numeric_expression )

Example:

SELECT CEILING(10.4);

FLOOR

Returns the largest integer less than or equal to the specified numeric expression.

FLOOR ( numeric_expression )

Example:

SELECT FLOOR(10.7);

ROUND

Returns a numeric value, rounded to the specified precision and scale.

ROUND ( numeric_expression , length [ , function ] )

Example:

SELECT ROUND(123.456, 2);

RAND

Returns a pseudo-random float value between 0 and 1.

RAND ( [ seed ] )

Example:

SELECT RAND();

@@SERVERNAME

Returns the name of the server on which the current instance of SQL Server is running.

@@SERVERNAME

Example:

SELECT @@SERVERNAME;

@@VERSION

Returns information about the version of Microsoft SQL Server and the operating system.

@@VERSION

Example:

SELECT @@VERSION;

DB_NAME

Returns the database name corresponding to the database ID.

DB_NAME ( [ database_id ] )

Example:

SELECT DB_NAME();

USER_NAME

Returns the user name associated with a specified user ID.

USER_NAME ( [ user_id ] )

Example:

SELECT USER_NAME(1);
Previous: Literals Next: Data Types