Chapter 2: Transact-SQL Functions

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 and time information.
Date functions	Perform computations on datetime, smalldatetime, date, and time values and their components, date parts.
Mathematical functions	Commonly needed for operations on mathematical data.
Security functions	Security-related information.
String functions	Operate on binary data, character strings, and expressions.
System functions	Retrieves special information from the database and database objects.
Text and image functions	Supply values commonly needed for operations on text, unitext, 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) with a specified cosine.
ascii	String	The ASCII code for the first character in an expression.
asin	Mathematical	The angle (in radians) with a specified sine.
atan	Mathematical	The angle (in radians) with a specified tangent.
atn2	Mathematical	The angle (in radians) with specified sine and cosine.
audit_event_name	Security	A description of an audit event
avg	Aggregate	The numeric average of all (distinct) values.
biginttohex	Datatype conversion	Returns the platform-independent hexadecimal equivalent of the specified integer.
case		Allows SQL expressions to be written for conditional values. case expressions can be used anywhere a value expression can be used.
cast	Datatype conversion	A specified value, converted to another datatype
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 with specified table and column IDs.
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 as an integer.
count_big	Aggregrate	The number of (distinct) non-null values as a bigint.
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_pages	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 with a specified ID number.
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.
hextobigint	Datatype conversion	The bigint value equivalent of a hexadecimal string
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.
identity_burn_max		The identity_burn_max value.
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.
is_quiesced		Indicates whether a database is in quiesce database mode. is_quiesced returns 1 if the database is quiesced and 0 if it is not.
is_sec_service_on	Security	1 if the security service is active; 0 if it is not.
isnull	System	Substitutes the value specified in expression2 when expression1 evaluates to NULL.
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 lowercase equivalent of the specified expression.
ltrim	String	The specified expression, trimmed of leading blanks
max	Aggregate	The highest value in a column.
min	Aggregate	The lowest value in a column.
month	Date	An integer that represents the month in the datepart of a specified date
mut_excl_roles	Security	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.
nullif		Allows SQL expressions to be written for conditional values. nullif expressions can be used anywhere a value expression can be used; alternative for a case expression.
object_id	System	The object ID of the specified object.
object_name	System	The name of the object with the specified object ID.
pagesize	Mathematical	Returns the page size, in bytes, for the specified object.
partition_id		Returns the partition ID of the specified data or index partition name.
partition_name		The explicit name of a new partition, partition_name returns the partition name of the specified data or index partition id.
patindex	String, Text, Unitext, 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	Security	1 if the user has the correct role to execute the procedure; 0 if the user does not have this role.
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_pages	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	Security	1 if role2 contains role1.
role_id	Security	The system role ID of the role with the name you specify.
role_name	Security	The name of a role with the system role ID you specify.
round	Mathematical	The value of the specified number, rounded to a given number of decimal places.
row_count	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	Security	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 where the server user ID is specified.
syb_quit	System	Terminates the connection.
syb_sendmsg	System	Sends a message to a User Datagram Protocol (UDP) port.
tan	Mathematical	The tangent of the specified angle (in radians).
tempdb_id	System	The database ID of the temporary database assigned to the specified spid
textptr	Text, Unitext, 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.
tran_dumptable_status	System	Returns a true/false indication of whether dump transaction is allowed.
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_pages	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	Date	An integer that represents the year in the datepart of a specified date.

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

View this book as PDF