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) with a 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) with a specified sine.
asin(sine)
Returns the angle (in radians) with a specified tangent.
atan(tangent )
Returns the angle (in radians) with specified sine and cosine.
atn2(sine, cosine)
Returns 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 expression.
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)
Supports conditional SQL expressions.
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
Returns the specified value, converted 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)
Supports conditional SQL expressions; 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.
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}]
Returns the specified value, converted to another datatype or a different datetime display format.
convert (datatype [(length) | (precision[, scale])] [null | not null], expression [, style])
Returns the cosine of the specified angle.
cos(angle)
Returns the cotangent of the specified angle.
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)
Returns a bigtime value representing the current time with microcecond precision.
current_bigdatetime()
Returns a bigtime value representing the current time with microcecond precision.
current_bigtime()
Returns the current date.
current_date()
Returns the current time.
current_time()
Returns 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 partition.
data_pages(dbid, object_id [, indid [, ptnid]])
Measures the amount of change in the data distribution since update statistics last ran.
datachange(object_name, partition_name, column_name)
Returns the actual length, in bytes, of the specified column or string.
datalength(expression)
Returns the date produced by adding or subtracting a given number of years, quarters, hours, or other date parts to the specified date.
dateadd(date_part, integer, {date | time | bigtime | datetime, |
bigdatetime})
Returns the difference between two dates.
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 specified datepart in the first argument of the specified date as an integer.
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 for the specified database.
db_attr('database_name' | database_ID | NULL, 'attribute')
Returns 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.
Returns the name of the database where the ID number is specified.
db_name([database_id])
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_recovery_status([database_ID | database_name])
Returns the size, in degrees, of an angle with the specified number of 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 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 (“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.
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()
Returns the current host computer name of the client process.
host_name()
Tracks the identity burn max value for a given table.
identity_burn_max(table_name)
Returns the name of the indexed column in the specified table or view.
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)
Cluster Edition 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])
Cluster Edition 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])
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(dbid)
Returns 1 if the security service is active and 0 if it is not.
is_sec_service_on(security_service_nm)
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 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 with the 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, returns the current value of the last-chance threshold (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(["context_name"])
Returns the locking scheme of the specified object as a string.
lockscheme(object_name)
lockscheme(object_id [, db_id])
Returns the natural logarithm of the specified number.
log(approx_numeric)
Returns the base 10 logarithm of the specified number.
log10(approx_numeric)
Returns the lowercase equivalent of the specified expression.
lower(char_expr | uchar_expr)
Returns the specified expression, trimmed 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.
newid([optionflag])
Retrieves the next identity value that is available for the next insert.
next_identity(table_name)
Supports conditional SQL expressions.
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; can be 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 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 information from the process status structure.
pssinfo(spid | 0, 'pss_field')
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 the specified role.
proc_role("role_name")
Returns the size, in radians, of an angle with the specified number of degrees.
radians(numeric)
Returns a random value between 0 and 1.
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.
replicate(char_expr | uchar_expr, integer_expr)
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.
reserved_pages(dbid, object_id[, indid[, ptnid]])
Returns the specified string with characters listed in reverse order.
reverse(expression | uchar_expr)
The rightmost part of the expression with the specified number of characters.
right(expression, integer_expr)
Removes a specific application context, or all application contexts.
rm_appcontext(“context_name”, “attribute_name”)
Returns 1 if role2 contains role1.
role_contain("role1", "role2")
Returns the system role ID of the name you specify.
role_id("role_name")
Returns the name of a system role ID you specify.
role_name(role_id)
Returns the value of the specified number, rounded to a 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])
Returns the specified expression, trimmed 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(“context_name, “attribute_name”, “attribute_value”)
Shows the login’s currently active system-defined roles.
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 specified angle (in radians).
sin(approx_numeric)
Generates values that can be used to order results based on collation behavior.
sortkey(char_expression | uchar_expression)
[, {collation_name | collation_ID}])
Returns a four-character code representing the way an expression sounds.
soundex(char_expr | uchar_expr)
Returns a string consisting of the specified number of single-byte spaces.
space(integer_expr)
Cluster Edition only – returns the instance ID on which the specified process id (spid) is running.
spid_instance_id(spid_value)
Returns the square of a specified value expressed as a float.
square(numeric_expression)
Returns the square root of the specified number.
sqrt(approx_numeric)
Is an alias for stddev_samp.
Is an alias for stddev_samp.
Is an alias for stddev_pop.
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.
str(approx_numeric[, length [, decimal]])
Replaces any instances of the second string expression that occur within the first string expression with a third expression.
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)
Returns the tangent of the specified angle (in radians).
tan(angle)
Reports the temporary database to which a given session is assigned.
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 or unitext column is valid; 0 if it is not.
textvalid("table_name.column_name", textpointer)
Returns a unichar expression having the value of the 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 high half of a surrogate pair (which should appear first in the pair); returns 0 otherwise.
uhighsurr(uchar_expr, start)
Returns 1 if the Unicode value at position start is the low half of a surrogate pair (which should appear second in the pair); returns 0 otherwise.
ulowsurr(uchar_expr, start)
Returns the uppercase equivalent of the specified string.
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.
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.
valid_name(character_expression[, maximum_length])
Returns 1 if the specified ID is a valid user or alias in at least one database on this Adaptive Server.
valid_user(server_user_id)
Is an alias for var_samp.
Computes the statistical variance of a population consisting of a numeric expression, as a double.
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_samp ( [ all | distinct] expression )
Is an alias for 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)
Extracts data from an XML document and returns it as a SQL table.
xmltable_expression ::= xmltable
( row_pattern passing xml_argument
columns column_definitions
options_parameter)
row_pattern ::= character_string_literal
xml_argument ::=
xml_expression | column_reference | variable_reference
column_definitions ::=
column_definition [ { , column_definition } ]
column_definition ::=
ordinality_column | regular_column
ordinality_column ::= column_name datatype for ordinality
regular_column ::=
column_name datatype [ default literal ] [null | not null]
[ path column_pattern ]
column_pattern ::= character_string_literal
options_parameter ::=[,] option option_string
options_string ::= basic_string_expression
Derived table syntax – returns a SQL table from within a SQL from clause.
from_clause ::= from table_reference [, table_reference]... table_reference ::= table_view_name | ANSI_join |derived_table table_view_name::= See the select command in Reference Manual Volume 2, "Commands." ANSI_join::= See the select command in Reference Manual Volume 2, "Commands." derived_table ::= (subquery) as table_name [ (column_name [, column_name]...)| xmltable_expression as table_name
Returns an integer that represents the year in the datepart of a specified date.
year(date_expression)
