This section provides brief descriptions and syntax for built-in functions. See Reference Manual: Building Blocks for complete descriptions, examples, and usage 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 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)
Returns the platform-independent 8-byte hexadecimal equivalent of the specified integer expression.
biginttohex (integer_expression)
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
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; 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}]
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 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 specific 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 expression)
Returns the difference between two dates.
datediff(datepart, date expression1,date expression2)
Returns the specified datepart (the first argument) of the specified date or time (the second argument) as a character string.
datename (datepart, date expression)
Returns the specified datepart in the first argument of the specified date (the second argument) as an integer.
datepart(date_part, date expression)
Returns an integer that represents the day in the datepart of a specified date.
day(date_expression)
Returns the ID number of the specified database.
db_id(database_name)
Returns the name of the database where the ID number is specified.
db_name([database_id])
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 is a built-in function 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).
insert t1 (c1, c2, c3) select c1, getutcdate(), getdate() from t2)
Returns information about whether the user has been granted the specified role.
has_role ("role_name"[, 0])
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, and can be up to 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 the platform-independent hexadecimal equivalent of the specified integer.
inttohex (integer_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)
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)
Returns the object ID of the specified object.
object_id(object_name)
Returns the name of the object with the object ID you specify.
object_name(object_id[, database_id])
Returns the page size, in bytes, for the specified object.
pagesize(object_name [, index_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] )
The explicit name of a new partition.
partition_name(indid, ptnid [, dbid])
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, which is generated using the specified seed value.
rand([integer])
Returns a string consisting of the specified expression repeated a given number of times.
replicate (char_expr | uchar_expr, integer_expr)
Reports the number of pages reserved to a table, index or a specific partition.
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)
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)
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)
Returns the character equivalent of the specified number.
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).
replace("string_expression1", "string_expression2", "string_expression3")
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
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, 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 on this Adaptive Server.
valid_user(server_user_id)
Returns an integer that represents the year in the datepart of a specified date.
year(date_expression)
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
