Types of functions

Table 2-1 lists the different types of Transact-SQL functions and describes the type of information each returns.

**Table 2-1: Types of Transact-SQL functions**
Type of function	Description
Aggregate functions	Generate summary values that appear as new columns or as additional rows in the query results.
Datatype conversion functions	Change expressions from one datatype to another and specify new display formats for date/time information.
Date functions	Do computations on datetime, smalldatetime, date and time values and their components, date parts.
Mathematical functions	Return values commonly needed for operations on mathematical data.
Security functions	Return security-related information.
String functions	Operate on binary data, character strings, and expressions.
System functions	Return special information from the database.
Text and image functions	Supply values commonly needed for operations on text and image data.

Table 2-2 lists the functions in alphabetical order.

**Table 2-2: List of Transact-SQL functions**
Function	Type	Return value
abs	Mathematical	The absolute value of an expression.
acos	Mathematical	The angle (in radians) whose cosine is specified.
ascii	String	The ASCII code for the first character in an expression.
asin	Mathematical	The angle (in radians) whose sine is specified.
atan	Mathematical	The angle (in radians) whose tangent is specified.
atn2	Mathematical	The angle (in radians) whose sine and cosine are specified.
avg	Aggregate	The numeric average of all (distinct) values.
ceiling	Mathematical	The smallest integer greater than or equal to the specified value.
char	String	The character equivalent of an integer.
charindex	String	Returns an integer representing the starting position of an expression.
char_length	String	The number of characters in an expression.
col_length	System	The defined length of a column.
col_name	System	The name of the column whose table and column IDs are specified.
compare	System	Returns the following values, based on the collation rules that you chose: 1 – indicates that char_expression1 is greater than char_expression2 0 – indicates that char_expression1 is equal to char_expression2 -1 – indicates that char_expression1 is less than char_expression2
convert	Datatype Conversion	The specified value, converted to another datatype or a different datetime display format.
cos	Mathematical	The cosine of the specified angle (in radians).
cot	Mathematical	The cotangent of the specified angle (in radians).
count	Aggregate	The number of (distinct) non-null values.
current_date	Date	Returns the current date.
current_time	Date	Returns the current time.
curunreservedpgs	System	The number of free pages in the specified disk piece.
data_pgs	System	The number of pages used by the specified table or index.
datalength	System	The actual length, in bytes, of the specified column or string.
dateadd	Date	The date produced by adding a given number of years, quarters, hours, or other date parts to the specified date.
datediff	Date	The difference between two date expressions.
datename	Date	The name of the specified part of a date expression.
datepart	Date	The integer value of the specified part of a date expression.
day	Date	Returns an integer that represents the day in the datepart of a specified date.
db_id	System	The ID number of the specified database.
db_name	System	The name of the database whose ID number is specified.
degrees	Mathematical	The size, in degrees, of an angle with a specified number of radians.
derived_stat	System	Returns derived statistics for the specified object and index.
difference	String	The difference between two soundex values.
exp	Mathematical	The value that results from raising the constant e to the specified power.
floor	Mathematical	The largest integer that is less than or equal to the specified value.
get_appcontext	Security	Returns the value of the attribute in a specified context.
getdate	Date	The current system date and time.
hextoint	Datatype Conversion	The platform-independent integer equivalent of the specified hexadecimal string.
host_id	System	Returns the client computer's operating system process ID for the current Adaptive Server client.
host_name	System	The current host computer name of the client process.
index_col	System	The name of the indexed column in the specified table or view.
index_colorder	System	Returns the column order
inttohex	Datatype Conversion	The platform-independent, hexadecimal equivalent of the specified integer.
isnull	System	Substitutes the value specified in expression2 when expression1 evaluates to NULL.
is_sec_service_on	Security	“1” if the security service is active; “0” if it is not.
isnull	String	The specified expression, trimmed of leading blanks.
lct_admin	System	Manages the last-chance threshold.
left	String	Returns a specified number of characters on the left end of a character string.
len	String	Returns the number of characters, not the number of bytes, of a specified string expression, excluding trailing blanks.
license_enabled	System	“1” if the feature’s license is enabled; “0” if it is not.
list_appcontext	Security	Lists all the attributes of all the contexts in the current session.
lockscheme	Mathematical	Returns the locking scheme of the specified object as a string.
log	Mathematical	The natural logarithm of the specified number.
log10	Mathematical	The base 10 logarithm of the specified number.
lower	String	The uppercase equivalent of the specified expression.
max	Aggregate	The highest value in a column.
min	Aggregate	The lowest value in a column.
mut_excl_roles	System	The mutual exclusivity between two roles.
newid	System	Generates human-readable, globally unique IDs (GUIDs) in two different formats, based on arguments you provide.
next_identity	System	Retrieves the next identity value that is available for the next insert.
object_id	System	The object ID of the specified object.
object_name	System	The name of the object whose object ID is specified.
pagesize	Mathematical	Returns the page size, in bytes, for the specified object.
patindex	String, Text and Image	The starting position of the first occurrence of a specified pattern.
pi	Mathematical	The constant value 3.1415926535897936.
power	Mathematical	The value that results from raising the specified number to a given power.
proc_role	System	1 if the user has the correct role to execute the procedure; 0 if the user does not have this role.
ptn_data_pgs	System	The number of data pages used by a partition.
radians	Mathematical	The size, in radians, of an angle with a specified number of degrees.
rand	Mathematical	A random value between 0 and 1, generated using the specified seed value.
replicate	String	A string consisting of the specified expression repeated a given number of times.
reserved_pgs	System	The number of pages allocated to the specified table or index.
reverse	String	The specified string, with characters listed in reverse order.
right	String	The part of the character expression, starting the specified number of characters from the right.
rm_appcontext	Security	Removes a specific application context, or all application contexts.
role_contain	System	1 if role2 contains role1.
role_id	System	The system role ID of the role whose name you specify.
role_name	System	The name of a role whose system role ID you specify.
round	Mathematical	The value of the specified number, rounded to a given number of decimal places.
rowcnt	System	An estimate of the number of rows in the specified table.
rtrim	String	The specified expression, trimmed of trailing blanks.
set_appcontext	Security	Sets an application context name, attribute name, and attribute value for a user session, defined by the attributes of a specified application.
show_role	System	The login’s currently active roles.
show_sec_services	Security	A list of the user’s currently active security services.
sign	Mathematical	The sign (+1 for positive, 0, or -1 for negative) of the specified value.
sin	Mathematical	The sine of the specified angle (in radians).
sortkey	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 Latin-character-based dictionary sort orders and case or accent sensitivity.
soundex	String	A 4-character code representing the way an expression sounds.
space	String	A string consisting of the specified number of single-byte spaces.
square	Mathematical	Returns the square of a specified value expressed as a float.
sqrt	Mathematical	The square root of the specified number.
str	String	The character equivalent of the specified number.
str_replace	String	Replaces any instances of the second string expression that occur within the first string expression with a third expression.
stuff	String	The string formed by deleting a specified number of characters from one string and replacing them with another string.
substring	String	The string formed by extracting a specified number of characters from another string.
sum	Aggregate	The total of the values.
suser_id	System	The server user’s ID number from the syslogins system table.
suser_name	System	The name of the current server user, or the user whose server user ID is specified.
syb_quit
syb_sendmsg		Sends a message to a User Datagram Protocol (UDP) port.
tan	Mathematical	The tangent of the specified angle (in radians).
tempdb_id
textptr	Text and Image	The pointer to the first page of the specified text column.
textvalid	Text and Image	1 if the pointer to the specified text column is valid; 0 if it is not.
to_unichar	String	A unichar expression having the value of the integer expression.
tsequal	System	Compares timestamp values to prevent update on a row that has been modified since it was selected for browsing.
uhighsurr	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.
ulowsurr	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.
upper	String	The uppercase equivalent of the specified string.
uscalar	String	The Unicode scalar value for the first Unicode character in an expression.
used_pgs	System	The number of pages used by the specified table and its clustered index.
user	System	The name of the current server user.
user_id	System	The ID number of the specified user or the current user.
user_name	System	The name within the database of the specified user or the current user.
valid_name	System	0 if the specified string is not a valid identifier; a number other than 0 if the string is valid.
valid_user	System	1 if the specified ID is a valid user or alias in at least one database on this Adaptive Server.
year

The following sections describe the types of functions in detail. The remainder of the chapter contains descriptions of the individual functions in alphabetical order.