These are very brief descriptions and syntax for built-in functions. See Reference Manual: Building Blocks for complete information.
Returns the absolute value of an expression.
abs(numeric_expression)
Returns the angle (in radians) of the specified cosine.
acos(cosine)
Returns the ASCII code for the first character in an expression.
ascii(char_expr | uchar_expr)
Returns the physical or virtual host on which Adaptive Server is running.
asehostname
Returns the angle (in radians) of the specified sine.
asin(sine)
Returns the angle (in radians) of the specified tangent.
atan(tangent )
Returns the angle (in radians) of the specified sine and cosine.
atn2(sine, cosine)
Calculates the numeric average of all (distinct) values.
avg([all | distinct] expression)
Returns a description of an audit event.
audit_event_name(event_id)
Determines what authentication mechanism is used by a specified logged in server process ID.
authmech ([spid])
Returns the platform-independent 8 byte hexadecimal equivalent of the specified integer.
biginttohex (integer_expression)
Converts a sequence of hexadecimal digits to a string of its equivalent alphanumeric characters or varbinary data.
select bintostr(sequence of hexadecimal digits)
Returns cache usage as a percentage of all objects in the cache to which the table belongs.
cache_usage(table_name)
Supports conditional SQL expressions; can be used anywhere a value expression can be used.
case when search_condition then expression [when search_condition then expression]... [else expression] end
case and values syntax:
case expression when expression then expression [when expression then expression]... [else expression] end
Converts the specified value to another datatype.
cast (expression as datatype [(length | precision[, scale])])
Returns the smallest integer greater than or equal to the specified value.
ceiling(value)
Returns the character equivalent of an integer.
char(integer_expr)
Returns the number of characters in an expression.
char_length(char_expr | uchar_expr)
Returns an integer representing the starting position of an expression.
charindex(expression1, expression2 [, start])
Supports conditional SQL expressions; can be used anywhere a value expression can be used; alternative for a case expression.
coalesce(expression, expression [, expression]...)
Returns the defined length of a column.
col_length(object_name, column_name)
Returns the name of the column where the table and column IDs are specified, and can be up to 255 bytes in length.
col_name(object_id, column_id [, database_id])
Allows you to directly compare two character strings based on alternate collation rules.
compare ({char_expression1|uchar_expression1},
{char_expression2|uchar_expression2}),
[{collation_name | collation_ID}]
Converts the specified value to another datatype or a different datetime display format.
convert (datatype [(length) | (precision[, scale])] [null | not null], expression [, style])
Returns the cosine of the angle specified in radians.
cos(angle)
Returns the cotangent of the angle specified in radians.
cot(angle)
Returns the number of (distinct) non-null values, or the number of selected rows as an integer.
count([all | distinct] expression)
Returns the number of (distinct) non-null values, or the number of selected rows as a bigint.
count_big([all | distinct] expression)
Explicitly creates a locator for a specified LOB then returns the locator.
create_locator (datatype, lob_expression)
Returns a bigtime value representing the current time with microcecond precision. The accuracy of the current time portion is limited by the accuracy of the system clock.
current_bigdatetime()
Returns a bigtime value representing the current time with microcecond precision. The accuracy of the current time portion is limited by the accuracy of the system clock.
current_bigtime()
Returns the current date.
current_date()
Returns the current time.
current_time()
Displays the number of free pages in the specified disk piece.
curunreservedpgs (dbid, lstart, unreservedpgs)
Returns the number of pages used by the specified table, index, or a specific partition. The result does not include pages used for internal structures.
data_pages(dbid, object_id [, indid [, ptnid]])
Measures the amount of change in the data distribution since update statistics last ran. Specifically, it measures the number of inserts, updates, and deletes that have occurred on the given object, partition, or column, and helps you determine if invoking update statistics would benefit the query plan.
datachange(object_name, partition_name, column_name)
Returns the actual length, in bytes, of the specified column or string.
datalength(expression)
Adds an interval to a specified date or time.
dateadd(date_part, integer, {date | time | bigtime | datetime, | bigdatetime})
Calculates the number of date parts between two specified dates or times.
datediff(datepart, {date, date | time, time | bigtime, bigtime | datetime, datetime | bigdatetime, bigdatetime}])
Returns the specified datepart of the specified date or time as a character string.
datename(datepart {date | time | bigtime | datetime | bigdatetime})
Returns the integer value of the specified part of a date expression
datepart(date_part {date | time | datetime | bigtime | bigdatetime}))
Returns an integer that represents the day in the datepart of a specified date.
day(date_expression)
Returns the durability, dml_logging, and template settings, and compression level for the specified database.
db_attr('database_name' | database_ID | NULL, 'attribute')
Displays the ID number of the specified database.
db_id(database_name)
(Cluster environments only) Returns the ID of the owning instance of a specified local temporary database. Returns NULL if the specified database is a global temporary database or a nontemporary database.
db_instanceid(database_id | database_name)
Displays the name of the database with the specified ID number.
db_name([database_id])
(Cluster environments only) Returns the recovery status of the specified database. Returns the recovery status of the current database if you do not include a value for database_ID or database_name.
db_recovery_status([database_ID | database_name])
Returns the size, in degrees, of the angle specified in radians.
degrees(numeric)
Returns derived statistics for the specified object and index.
derived_stat("object_name" | object_id,
index_name | index_id,
["partition_name" | partition_id,]
“statistic”)
Returns the difference between two soundex values.
difference(expr1,expr2)
Returns the number of data-only-locked (DOL) tables in the specified database that contain variable-length columns wider than 8191 bytes. Returns 0 when there are no wide, variable-length columns and you can safely perform the downgrade.
dol_downgrade_check('database_name', target_version)
Calculates the value that results from raising the constant to the specified power.
exp(approx_numeric)
Returns the largest integer that is less than or equal to the specified value.
floor(numeric)
Returns the value of the attribute in a specified context. get_appcontext is provided by the Application Context Facility (ACF).
get_appcontext (“context_name”, “attribute_name”)
Returns the current system date and time.
getdate()
Returns a date and time where the value is in Universal Coordinated Time (UTC). getutcdate is calculated each time a row is inserted or selected.
getutcdate()
Returns information about whether the user has been granted the specified role.
has_role ("role_name", option)
Produces a fixed-length hash value expression.
hash(expression , [algorithm])
Produces a fixed-length, hash value expression.
hashbytes(algorithm, expression[, expression...] [, using options])
Returns the bigint value equivalent of a hexadecimal string
hextobigint(hexadecimal_string)
Returns the platform-independent integer equivalent of a hexadecimal string.
hextoint(hexadecimal_string)
Returns the client computer’s operating system process ID for the current Adaptive Server client.
host_id()
Displays the current host computer name of the client process.
host_name()
(Cluster environments only) Returns the id of the named instance, or the instance from which it is issued if you do not provide a value for name.
instance_id([name])
Tracks the identity burn max value for a given table. This function returns only the value; it does not perform an update.
identity_burn_max(table_name)
Displays the name of the indexed column in the specified table or view to a maximum of 255 bytes in length.
index_col(object_name, index_id, key_#[, user_id])
Returns the column order.
index_colorder(object_name, index_id, key_#[, user_id])
Returns an index name, when you provide the index ID, the database ID, and the object on which the index is defined.
index_name(dbid, objid, indid)
Returns the platform-independent hexadecimal equivalent of the specified integer.
inttohex(integer_expression)
Determines whether an input expression is a valid datetime value.
isdate(character_expression)
Determines if an expression is a valid numeric datatype.
isnumeric (character_expression)
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_quiesced(dbid)
Determines whether a particular security service is enabled. Returns 1 if the service is enabled; otherwise, returns 0.
is_sec_service_on(security_service_nm)
Returns 0 if Adaptive Server is not running in single-user mode. Returns 1 if Adaptive Server is running in single-user mode.
is_singleusermode()
Substitutes the value specified in expression2 when expression1 evaluates to NULL.
isnull(expression1, expression2)
Determines if an expression is a valid numeric datatype.
isnumeric (character_expression)
(Cluster environments only) Returns the name for the Adaptive Server whose id you provide, or the name of the Adaptive Server from which it is issued if you do not provide a value for id.
instance_name([id])
(Cluster environments only) Returns the ID of the logical cluster whose name you provide, or the current logical cluster if you do not provide a name.
lc_id(logical_cluster_name)
(Cluster environments only) Returns the name of the logical cluster whose id you provide, or the current logical cluster if you do not provide an ID.
lc_name([logical_cluster_ID])
Manages the last-chance threshold (LCT). It returns the current value of the LCT and aborts transactions in a transaction log that has reached its LCT.
lct_admin({{"lastchance" | "logfull" | "reserved_for_rollbacks"},
database_id
|"reserve", {log_pages | 0 }
| "abort", process-id [, database-id]})
Returns a specified number of characters on the left end of a character string.
left(character_expression, integer_expression)
Returns the number of characters, not the number of bytes, of a specified string expression, excluding trailing blanks.
len(string_expression)
Returns 1 if a feature’s license is enabled, 0 if the license is not enabled, or NULL if you specify an invalid license name.
license_enabled("ase_server" | "ase_ha" | "ase_dtm" | "ase_java" |
"ase_asm")
Lists all the attributes of all the contexts in the current session. list_appcontext is provided by the ACF.
list_appcontext(["context_name"])
Identifies a binary value as a locator literal.
locator_literal(locator_type, literal_locator)
Determines whether a LOB locator is valid.
locator_valid (locator_descriptor)
Returns the locking scheme of the specified object as a string.
lockscheme(object_name)
lockscheme(object_id [, db_id])
Calculates the natural logarithm of the specified number.
log(approx_numeric)
Calculates the base 10 logarithm of the specified number.
log10(approx_numeric)
Converts uppercase characters to lowercase.
lower(char_expr | uchar_expr)
Returns the ID associated with the specified name, or returns the ID of the current login.
lprofile_id(name),
Returns the name associated with the specified ID, or returns the name of the current login
lprofile_id(ID),
Trims the specified expression of leading blanks.
ltrim(char_expr | uchar_expr)
Returns the highest value in an expression.
max(expression)
Returns the lowest value in a column.
min(expression)
Returns an integer that represents the month in the datepart of a specified date.
month(date_expression)
Returns information about the mutual exclusivity between two roles.
mut_excl_roles (role1, role2 [membership | activation])
Generates human-readable, globally unique IDs (GUIDs) in two different formats, based on arguments you provide. The length of the human-readable format of the GUID value is either 32 bytes (with no dashes) or 36 bytes (with dashes).
newid([optionflag])
Retrieves the next identity value that is available for the next insert.
next_identity(table_name)
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.
nullif(expression, expression)
Reports the table’s current logging mode, depending on the session, table and database-wide settings.
object_attr(table_name, string)
Returns the object ID of the specified object.
object_id(object_name)
Returns the name of the object with the object ID you specify; up to 255 bytes in length.
object_name(object_id[, database_id])
Returns an object’s owner ID.
object_owner_id(object_id[, database_id])
Returns the page size, in bytes, for the specified object.
pagesize(object_name[, ])
pagesize(object_id[,db_id[, index_id]])
Returns the partition ID of the specified data or index partition name.
partition_id(table_name, partition_name[,index_name])
Returns the explicit name of a new partition, partition_name returns the partition name of the specified data or index partition id.
partition_name(indid, ptnid[, dbid])
Displays the object ID for a specified partition ID and database ID.
partition_object_id(partition_id [, database_id ] )
Returns the starting position of the first occurrence of a specified pattern.
patindex("%pattern%", char_expr|uchar_expr[, using
{bytes | characters | chars}])
Returns the constant value 3.1415926535897936.
pi()
Returns the value that results from raising the specified number to a given power.
power(value, power)
Returns information about whether the user has been granted a specified role.
proc_role("role_name")
Returns information from the Adaptive Server process status structure (pss).
pssinfo(spid | 0, 'pss_field')
Converts degrees to radians. Returns the size, in radians, of an angle with the specified number of degrees.
radians(numeric)
Returns a random float value between 0 and 1 using the specified (optional) integer as a seed value.
rand([integer])
Returns a random value between 0 and 1, which is generated using the specified seed value, and computed for each returned row when used in the select list.
rand2([integer])
Returns a string consisting of the specified expression repeated a given number of times, or as many as can fit into a 16KB space, whichever is less.
replicate(char_expr | uchar_expr, integer_expr)
reserve_identity allows a process to reserve a block of identity values for use by that process.
reserve_identity (table_name, number_of_values)
Reports the number of pages reserved for a database, object, or index. The result includes pages used for internal structures.
reserved_pages(dbid, object_id[, indid[, ptnid]])
Dereferences a locator, and returns the LOB referenced by that locator.
return_lob (datatype, locator_descriptor)
Returns the specified string with characters listed in reverse order.
reverse(expression | uchar_expr)
Returns the part of the character or binary expression starting at the specified number of characters from the right. Return value has the same datatype as the character expression.
right(expression, integer_expr)
Removes a specific application context, or all application contexts. rm_appcontext is provided by the ACF.
rm_appcontext(“context_name”, “attribute_name”)
Determines whether a specified role is contained within another specified role.
role_contain("role1", "role2")
Returns the role ID of the specified role name.
role_id("role_name")
Returns the role name of the specified role ID.
role_name(role_id)
Returns the value of the specified number, rounded to the specified number of decimal places.
round(number, decimal_places)
Returns an estimate of the number of rows in the specified table.
row_count(dbid, object_id [,ptnid] [, “option”])
Trims the specified expression of trailing blanks.
rtrim(char_expr | uchar_expr)
(Cluster environments only) Returns 1 if the system is currently in temporary database configuration mode; if not, returns 0.
sdc_intempdbconfig()
Sets an application context name, attribute name, and attribute value for a user session, defined by the attributes of a specified application. set_appcontext is a provided by the ACF.
set_appcontext(“context_name, “attribute_name”, “attribute_value”)
Overwrites some or all of a large object (LOB).
setdata(locator_name, offset_value, new_value)
Displays, in XML, the executing query plan for queries in the statement cache.
show_cached_plan_in_xml(statement_id, plan_id, [level_of_detail])
Returns the text of a query in XML format.
show_dynamic_params_in_xml(object_id)
Displays the currently active system-defined roles of the current login.
show_role()
Lists the security services that are active for the session.
show_sec_services()
Returns the sign (1 for positive, 0, or -1 for negative) of the specified value.
sign(numeric)
Returns the sine of the angle specified in radians.
sin(approx_numeric)
Generates 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.
sortkey(char_expression | uchar_expression)[, {collation_name |
collation_ID}])
Returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double-byte Roman letters.
soundex(char_expr | uchar_expr)
Returns a string consisting of the specified number of single-byte spaces.
space(integer_expr)
(Cluster environments only) Returns the instance ID on which the specified process id (spid) is running.
spid_instance_id(spid_value)
Calculates the square of a specified value expressed as a float.
square(numeric_expression)
Calculates the square root of the specified number.
sqrt(approx_numeric)
Computes the standard deviation of a sample consisting of a numeric expression, as a double. stddev and stdev are aliases for stddev_samp.
Computes the standard deviation of a sample consisting of a numeric expression, as a double. stddev and stdev are aliases for stddev_samp.
Computes the standard deviation of a population consisting of a numeric expression, as a double. stdevp is an alias for stddev_pop, and uses the same syntax.
Computes the standard deviation of a population consisting of a numeric expression, as a double.
stddev_pop ( [ all | distinct ] expression )
Computes the standard deviation of a sample consisting of a numeric expression as a double..
stddev_samp ( [ all | distinct ] expression )
Returns the character equivalent of the specified number, and pads the output with a character or numeric to the specified length.
str(approx_numeric[, length [, decimal]])
Replaces any instances of the second string expression (string_expression2) that occur within the first string expression (string_expression1) with a third expression (string_expression3).
str_replace("string_expression1", "string_expression2", "string_expression3")
Converts a sequence of alphanumeric characters to their equivalent hexadecimal digits.
select strtobin(“string of valid alphanumeric characters”)
Returns the string formed by deleting a specified number of characters from one string and replacing them with another string.
stuff(char_expr1 | uchar_expr1, start, length, char_expr2 | uchar_expr2)
Returns the string formed by extracting the specified number of characters from another string.
substring(expression, start, length)
Returns the total of the values.
sum([all | distinct] expression)
Returns the server user’s ID number from the syslogins table.
suser_id([server_user_name])
Returns the name of the current server user, or the user whose server ID is specified.
suser_name([server_user_id])
Terminates the connection.
syb_quit()
(UNIX only) Sends a message to a User Datagram Protocol (UDP) port.
syb_sendmsg ip_address, port_number, message
(Cluster environments only) Returns the id of the effective local system temporary database of the specified instance. Returns the id of the effective local system temporary database of the current instance when instance_id is not specified.
sys_tempdbid(instance_id)
Calculates the tangent of the angle specified in radians.
tan(angle)
Reports the temporary database to which a given session is assigned. The input of the tempdb_id function is a server process ID, and its output is the temporary database to which the process is assigned. If you do not provide a server process, tempdb_id reports the dbid of the temporary database assigned to the current process.
tempdb_id()
Returns a pointer to the first page of a text, image, or unitext column.
textptr(column_name)
Returns 1 if the pointer to the specified text, unitext, in-row, and off-row LOB columns is valid; 0 if it is not.
textvalid("table_name.column_name", textpointer)
Returns a unichar expression having the value of the specified integer expression.
to_unichar(integer_expr)
Returns a true/false indication of whether dump transaction is allowed.
tran_dumpable_status("database_name")
Compares timestamp values to prevent update on a row that has been modified since it was selected for browsing.
tsequal(browsed_row_timestamp, stored_row_timestamp)
Returns 1 if the Unicode value at position start is the higher half of a surrogate pair (which should appear first in the pair). Otherwise, returns 0. This function allows you to write explicit code for surrogate handling.
uhighsurr(uchar_expr, start)
Returns 1 if the Unicode value at start is the low half of a surrogate pair (which should appear second in the pair). Otherwise, returns 0. This function allows you to explicitly code around the adjustments performed by substr(), stuff(), and right().
ulowsurr(uchar_expr, start)
Converts specified lowercase string to the uppercase equivalent.
upper(char_expr)
Returns the Unicode scalar value for the first Unicode character in an expression.
uscalar(uchar_expr)
Reports the number of pages used by a table, an index, or a specific partition. Unlike data_pages, used_pages does include pages used for internal structures. This function replaces the used_pgs function used in versions of Adaptive Server earlier than 15.0.
used_pages(dbid, object_id[, indid[, ptnid]])
Returns the name of the current user.
user
Returns the ID number of the specified user or of the current user in the database.
user_id([user_name])
Returns the name within the database of the specified user or of the current user.
user_name([user_id])
Returns 0 if the specified string is not a valid identifier or a number other than 0 if the string is a valid identifier, and can be up to 255 bytes in length.
valid_name(character_expression[, maximum_length])
Returns 1 if the specified ID is a valid user or alias in at least one database.
valid_user(server_user_id [, database_id])
Computes the statistical variance of a sample consisting of a numeric expression, as a double, and returns the variance of a set of numbers. var and variance are aliases of var_samp.
Computes the statistical variance of a population consisting of a numeric expression, as a double. varp is an alias for var_pop, and uses the same syntax.
var_pop ( [all | distinct] expression )
Computes the statistical variance of a sample consisting of a numeric-expression, as a double, and returns the variance of a set of numbers. var and variance are aliases of var_samp, and use the same syntax.
var_samp ( [ all | distinct] expression )
Computes the statistical variance of a sample consisting of a numeric expression, as a double, and returns the variance of a set of numbers. var and variance are aliases of var_samp.
Computes the statistical variance of a population consisting of a numeric expression, as a double. varp is an alias of var_pop.
(Cluster environments only) Queries the current workload metric for the instance you specify, or updates the metric for the instance you specify.
workload_metric( instance_id | instance_name [, new_value ] )
Returns the binary version of the bqual component of an ASCII XA transaction ID.
xa_bqual(xid, 0)
Returns the binary version of the gtrid component of an ASCII XA transaction ID.
xa_gtrid(xactname, int)
(Cluster environments only) Determines whether or not a connection can process an external transaction.
xact_connmigrate_check(“txn_name”)
(Cluster environments only) Returns the instance ID on which the distributed transaction is running.
xact_owner_instance(XID)
Returns an integer that represents the year in the datepart of a specified date.
year(date_expression)
