Table 21 lists the different types of TransactSQL functions and describes the type of information each returns.
Type of function 
Description 

Generate summary values that appear as new columns or as additional rows in the query results. 

Change expressions from one datatype to another and specify new display formats for date/time information. 

Do computations on datetime and smalldatetime values and their components, date parts. 

Return values commonly needed for operations on mathematical data. 

Return securityrelated information. 

Operate on binary data, character strings, and expressions. 

Return special information from the database. 

Supply values commonly needed for operations on text and image data. 
Table 22 lists the functions in alphabetical order.
Function 
Type 
Return value 

Mathematical 
The absolute value of an expression. 

Mathematical 
The angle (in radians) whose cosine is specified. 

String 
The ASCII code for the first character in an expression. 

Mathematical 
The angle (in radians) whose sine is specified. 

Mathematical 
The angle (in radians) whose tangent is specified. 

Mathematical 
The angle (in radians) whose sine and cosine are specified. 

Aggregate 
The numeric average of all (distinct) values. 

Mathematical 
The smallest integer greater than or equal to the specified value. 

String 
The character equivalent of an integer. 

String 
Returns an integer representing the starting position of an expression. 

String 
The number of characters in an expression. 

System 
The defined length of a column. 

System 
The name of the column whose table and column IDs are specified. 

System 
Returns the following values, based on the collation rules that you chose:


Datatype Conversion 
The specified value, converted to another datatype or a different datetime display format. 

Mathematical 
The cosine of the specified angle (in radians). 

Mathematical 
The cotangent of the specified angle (in radians). 

Aggregate 
The number of (distinct) nonnull values. 

System 
The number of free pages in the specified disk piece. 

System 
The number of pages used by the specified table or index. 

System 
The actual length, in bytes, of the specified column or string. 

Date 
The date produced by adding a given number of years, quarters, hours, or other date parts to the specified date. 

Date 
The difference between two dates. 

Date 
The name of the specified part of a datetime value. 

Date 
The integer value of the specified part of a datetime value. 

System 
The ID number of the specified database. 

System 
The name of the database whose ID number is specified. 

Mathematical 
The size, in degrees, of an angle with a specified number of radians. 

String 
The difference between two soundex values. 

Mathematical 
The value that results from raising the constant e to the specified power. 

Mathematical 
The largest integer that is less than or equal to the specified value. 

Date 
The current system date and time. 

Datatype Conversion 
The platformindependent integer equivalent of the specified hexadecimal string. 

System 
The host process ID of the client process. 

System 
The current host computer name of the client process. 

System 
The name of the indexed column in the specified table or view. 

Datatype Conversion 
The platformindependent, hexadecimal equivalent of the specified integer. 

System 
Substitutes the value specified in expression2 when expression1 evaluates to NULL. 

Security 
“1” if the security service is active; “0” if it is not. 

String 
The specified expression, trimmed of leading blanks. 

System 
Manages the lastchance threshold. 

System 
“1” if the feature’s license is enabled; “0” if it is not. 

Mathematical 
The natural logarithm of the specified number. 

Mathematical 
The base 10 logarithm of the specified number. 

String 
The uppercase equivalent of the specified expression. 

Aggregate 
The highest value in a column. 

Aggregate 
The lowest value in a column. 

System 
The mutual exclusivity between two roles. 

System 
The object ID of the specified object. 

System 
The name of the object whose object ID is specified. 

String, Text and Image 
The starting position of the first occurrence of a specified pattern. 

Mathematical 
The constant value 3.1415926535897936. 

Mathematical 
The value that results from raising the specified number to a given power. 

System 
1 if the user has the correct role to execute the procedure; 0 if the user does not have this role. 

System 
The number of data pages used by a partition. 

Mathematical 
The size, in radians, of an angle with a specified number of degrees. 

Mathematical 
A random value between 0 and 1, generated using the specified seed value. 

String 
A string consisting of the specified expression repeated a given number of times. 

System 
The number of pages allocated to the specified table or index. 

String 
The specified string, with characters listed in reverse order. 

String 
The part of the character expression, starting the specified number of characters from the right. 

System 
1 if role2 contains role1. 

System 
The system role ID of the role whose name you specify. 

System 
The name of a role whose system role ID you specify. 

Mathematical 
The value of the specified number, rounded to a given number of decimal places. 

System 
An estimate of the number of rows in the specified table. 

String 
The specified expression, trimmed of trailing blanks. 

System 
The login’s currently active roles. 

Security 
A list of the user’s currently active security services. 

Mathematical 
The sign (+1 for positive, 0, or 1 for negative) of the specified value. 

Mathematical 
The sine of the specified angle (in radians). 

System 
Values that can be used to order results based on collation behavior, which allows you to work with character collation behaviors beyond the default set of Latincharacterbased dictionary sort orders and case or accent sensitivity. 

String 
A 4character code representing the way an expression sounds. 

String 
A string consisting of the specified number of singlebyte spaces. 

Mathematical 
The square root of the specified number. 

String 
The character equivalent of the specified number. 

String 
The string formed by deleting a specified number of characters from one string and replacing them with another string. 

String 
The string formed by extracting a specified number of characters from another string. 

Aggregate 
The total of the values. 

System 
The server user’s ID number from the syslogins system table. 

System 
The name of the current server user, or the user whose server user ID is specified. 

Sends a message to a User Datagram Protocol (UDP) port. 

Mathematical 
The tangent of the specified angle (in radians). 

Text and Image 
The pointer to the first page of the specified text column. 

Text and Image 
1 if the pointer to the specified text column is valid; 0 if it is not. 

String 
A unichar expression having the value of the integer expression. 

System 
Compares timestamp values to prevent update on a row that has been modified since it was selected for browsing. 

String 
1 if the Unicode value at position start is the high half of a surrogate pair (which should appear first in the pair); otherwise 0. 

String 
1 if the Unicode value at position start is the low half of a surrogate pair (which should appear second in the pair); otherwise 0. 

String 
The uppercase equivalent of the specified string. 

String 
The Unicode scalar value for the first Unicode character in an expression. 

System 
The number of pages used by the specified table and its clustered index. 

System 
The name of the current server user. 

System 
The ID number of the specified user or the current user. 

System 
The name within the database of the specified user or the current user. 

System 
0 if the specified string is not a valid identifier; a number other than 0 if the string is valid. 

System 
1 if the specified ID is a valid user or alias in at least one database on this Adaptive Server. 
The following sections describe the types of functions in detail. The remainder of the chapter contains descriptions of the individual functions in alphabetical order.