Functions return special information from the system about users, expressions, a database or database objects, and so on. Aggregate functions return summary values. Other built-in functions perform various operations and are often used on their own or as part of a stored procedure or program. When used in SQL statements, functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed.
The built-in functions detailed below are Transact-SQL extensions to SQL. They can be divided into the following specific categories. For more information, see the Expressions topic and the Text and Image Manipulation topic.
Aggregate functions return summary values. These are the aggregate functions:
| AVG | COUNT(*) | MIN |
| COUNT | MAX | SUM |
Date functions compute datetime values and their components, dataparts. These are the date functions:
| DATEADD | DATENAME | GETDATE |
| DATEDIFF | DATEPART |
Mathematical functions perform operations on numeric data. These are the mathematical functions:
| ABS | DEGREES | RAND |
| ACOS | EXP | ROUND |
| ASIN | FLOOR | SIGN |
| ATAN | LOG | SIN |
| ATN2 | LOG10 | SQRT |
| CEILING | PI | TAN |
| COS | POWER | |
| COT | RADIANS |
Niladic functions allow a system-supplied value to be inserted into a table when no value is specified. ANSI-standard niladic functions are used in DEFAULT constraints. These niladic functions are supported:
| CURRENT_TIMESTAMP | SYSTEM_USER |
| CURRENT_USER | USER |
| SESSION_USER |
For details about these functions, see the CREATE TABLE and ALTER TABLE statements.
String functions perform operations on binary data, character strings, or expressions. These are the string functions:
| LTRIM | SOUNDEX | |
| ASCII | PATINDEX | SPACE |
| CHAR | REPLICATE | STR |
| CHARINDEX | REVERSE | STUFF |
| DIFFERENCE | RIGHT | SUBSTRING |
| LOWER | RTRIM | UPPER |
System functions return special information from the database. These are the system functions:
| COALESCE | HOST_NAME | OBJECT_NAME |
| COL_LENGTH | IDENT_INCR | STATS_DATE |
| COL_NAME | IDENT_SEED | SUSER_ID |
| DATALENGTH | INDEX_COL | SUSER_NAME |
| DB_ID | ISNULL | USER_ID |
| DB_NAME | NULLIF | USER_NAME |
| GETANSINULL | HOST_ID | OBJECT_ID |
For details about the COALESCE and NULLIF functions, see the CASE statement.
Text and image functions perform operations on text and image data. These are the text and image functions:
| PATINDEX | TEXTPTR | |
| SET TEXTSIZE | TEXTVALID |
For details about these functions, see the text and image Manipulation topic.
The type-conversion function transforms expressions from one datatype into another. This is the type-conversion function:
| CONVERT |