SQL Server contains several built-in functions that can be used with the Transact SQL language. This appendix categorizes the built-in functions.
The following sections cover aggregate functions.
Parameter Explanation
The ALL parameter applies the aggregate function to all values. ALL is the default. The DISTINCT parameter applies the aggregate function to only distinct values. The expression parameter is a column name.
Sum of values in a column.NULLs are ignored.
Count number of non-NULL values in a column. NULLs are ignored.
Count number of rows. NULLs are counted.
Maximum value for a column. NULLs are ignored.
Minimum value for a column. NULLs are ignored.
Sum of values for a column. NULLs are ignored.
The following sections cover date functions.
Parameter Explanation
The date parameter is a valid date. The datepart parameter is a date part or abbreviation. (See Table D.1 for valid date part abbreviations.) The number parameter is a valid number.
Date Part | Abbreviation | Values |
year | yy | 1753-9999 |
quarter | 1-4 | |
month | mm | 1-12 |
day of year | dy | 1-366 |
day | dd | 1-31 |
week | wk | 1-53 |
weekday | dw | 1-7 (Sun.-Sat.) |
hour | hh | 0-23 |
minute | mi | 0-59 |
second | ss | 0-59 |
millisecond | ms | 0-999 |
Returns a date incremented by the specified value.
Returns the date part difference between a number and a date.
Returns the date part of a specified date as a string.
Returns the date part of a specified date as an integer.
Returns the current date and time.
Returns 1 if test_expr is a valid date; returns 0 if the expression is not a valid date.
The following sections cover mathematical functions.
Returns the absolute value of a specified expression.
Returns the angle in radians of a cosine expression.
Returns the angle in radians of a sine expression.
Returns the angle in radians of a tangent expression.
Returns the angle in radians of a tangent expression.
Returns a rounded-up integer based on the specified expression.
Returns the cosine of a specified expression.
Returns the cotangent of a specified expression.
Returns the degrees of a specified expression.
Returns the exponential value of a specified expression.
Returns a rounded-down integer based on the specified expression.
Returns 1 if numeric_expr is a valid numeric expression; returns 0 if the expression is not a valid numeric expression.
Returns the natural logarithm of a specified expression.
Returns the base-10 logarithm of a specified expression.
Returns pi.
Returns the value of numeric_expr to the power of y.
Returns the radians of a specified expression.
Returns a random float number between 0 and 1. Use the optional integer_expr as the seed value.
Returns a number rounded to the precision specified by integer_expr.
Returns +1, 0, or -1 based on the sign of the expression.
Returns the sine of an angle specified in radians.
Returns the square root of a specified expression.
Returns the tangent of an angle specified in radians.
Niladic functions are new to SQL Server 6.x. These functions allow default values to be inserted into a table. Before version 6.x, you had to use triggers to insert these types of default values. For more information about these functions, see the CREATE TABLE statement in SQL Server's Books Online.
Returns the current date and time.
Returns the name of the person doing the insert.
Returns the name of the person doing the insert.
Returns the login ID of the person doing the insert (same as SESSION_USER).
Returns the name of the person doing the insert (same as SESSION_USER and SYSTEM_USER).
The following sections cover string functions.
Concatenates two or more nonnumeric expressions.
Returns the corresponding ASCII code value of a specified expression.
Returns the corresponding character from the specified ASCII code value. The code must be between 0 and 255.
Returns the first position of a pattern within an expression.
Determines the similarities between two strings and returns a value rating the similarities on a scale of 0 to 4, with 4 being the best match.
Converts an expression to lowercase.
Removes leading spaces.
Returns the first position of a pattern in the specified expression.
Replicates a character expression integer_expr number of times.
Returns a reversed expression.
Returns integer_expr number of characters from a character expression starting at the right side of char_expr.
Removes trailing spaces.
Returns a four-digit SOUNDEX code.
Returns integer_expr number of spaces.
Returns a character string converted from numeric data.
Stuffs char_expr1 into char_expr2.
Returns a portion of a string expression defined by the start value and the length value.
Converts an expression to uppercase.
The following sections cover system functions.
Returns the application name for the current connection.
Returns the first non-NULL expression in a list of expressions.
Returns the length of a column in a table.
Returns the name of a column based on ID.
Returns the length of a specified expression.
Returns the database identification number of a specified database name.
Returns the database name for a specified database ID.
Returns the nullability setting for a database.
Returns the workstation identification number.
Returns the workstation name.
Returns the increment value used for the creation of an identity column.
Returns the seed value used for the creation of an identity column.
Returns the index name for an indexed column.
Replaces a NULL expression with a specified value.
Returns NULL when expression1 equals expression2.
Returns the ID for a specified object name.
Returns the name for a specified object ID.
Returns a date indicating when an index's statistics were last updated.
Returns the login ID for a specified login name.
Returns the login name for a specified login ID.
Returns the user's database name for a specified user ID (same as USER_NAME()).
Returns the user's database ID for a specified user name.
Returns the user's database name for a specified user ID.
The following sections cover text and image functions.
Returns the length of a specified expression.
Returns the first position of a pattern in the specified expression.
Returns the text-pointer value.
Returns 1 if the text pointer is valid and 0 if the pointer is invalid.
The following section covers the CONVERT function.
Parameter Explanation
The datatype parameter is any valid SQL Server datatype. The length parameter is used with char, varchar, binary, and varbinary datatypes. The expression parameter is the value to convert. The style parameter is the date format to use with datetime or smalldatetime data conversion. (See Table D.2 for valid styles.)
Without Century(yy) | With Century (yyyy) | Standard | Output |
-- | 0 or 100 | Default | mon dd yyyy hh:miAM (or PM) |
1 | 101 | USA | mm/dd/yy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | British/French | dd/mm/yy |
4 | 104 | German | dd.mm.yy |
5 | 105 | Italian | dd-mm-yy |
6 | 106 | -- | dd mon yy |
7 | 107 | -- | mon dd, yy |
8 | 108 | -- | hh:mm:ss |
-- | 9 or 109 | Default + milliseconds | mon dd yyyy (hh:mi:ss:mmmAM (or PM)) |
10 | 110 | USA | mm-dd-yy |
11 | 111 | JAPAN | yy/mm/dd |
12 | 112 | ISO | yymmdd |
-- | 13 or 113 | European default + | dd mon yyyy millisecondshh:mi:ss:mmm(24h) |
14 | 114 | -- | hh:mi:ss:mmm(24h) |
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.