SQL Functions Reference
Aggregate Functions
AVG() Returns the average value of an expression.
Syntax
AVG ( expression )Description
Calculates the average of all the values in a specified column or expression.
Parameters
expression: An expression that returns a numeric value.
Example
SELECT AVG(UnitPrice) FROM Products;COUNT() Returns the number of items in a group.
Syntax
COUNT ( { [ ALL | DISTINCT ] expression | * } )Description
Counts the number of rows or non-NULL values in a column.
Parameters
expression: The expression that you want to count.*: Specifies that COUNT(*) counts all rows in the specified table, regardless of NULL values.
Example
SELECT COUNT(*) FROM Customers;MAX() Returns the maximum value in a set of values.
Syntax
MAX ( expression )Description
Returns the maximum value from the set of values in a column.
Parameters
expression: An expression that returns a value from the same data type category as the column.
Example
SELECT MAX(OrderDate) FROM Orders;MIN() Returns the minimum value in a set of values.
Syntax
MIN ( expression )Description
Returns the minimum value from the set of values in a column.
Parameters
expression: An expression that returns a value from the same data type category as the column.
Example
SELECT MIN(Price) FROM Products;SUM() Returns the sum of values.
Syntax
SUM ( expression )Description
Calculates the sum of all the values in a specified column or expression.
Parameters
expression: An expression that returns a numeric value.
Example
SELECT SUM(Quantity) FROM OrderDetails;Scalar Functions
ABS() Returns the absolute value of a number.
Syntax
ABS ( numeric_expression )Description
Returns the absolute, positive value of a numeric expression.
Parameters
numeric_expression: A constant, variable, or numeric expression that returns a value of the integer, decimal, money, float, or real data type.
Example
SELECT ABS(-10.5);LEN() Returns the number of characters in a string.
Syntax
LEN ( string_expression )Description
Returns the number of characters in a character string expression.
Parameters
string_expression: The expression of character string data.
Example
SELECT LEN('SQL Server');GETDATE() Returns the current database system date and time.
Syntax
GETDATE ( )Description
Returns the current date and time as a datetime value.
Parameters
None.
Example
SELECT GETDATE();String Functions
CONCAT() Concatenates two or more strings.
Syntax
CONCAT ( string_expression [, string_expression ]... )Description
Concatenates two or more string expressions into one string.
Parameters
string_expression: The string to be concatenated.
Example
SELECT CONCAT('Microsoft', ' ', 'SQL');UPPER() Converts a string to uppercase.
Syntax
UPPER ( character_expression )Description
Converts a specified string expression to uppercase.
Parameters
character_expression: The character expression to convert.
Example
SELECT UPPER('sql server');LOWER() Converts a string to lowercase.
Syntax
LOWER ( character_expression )Description
Converts a specified string expression to lowercase.
Parameters
character_expression: The character expression to convert.
Example
SELECT LOWER('SQL Server');