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.