Transact-SQL Functions

Often used as part of a stored procedure or program, functions are allowed in the select list, in the where clause, and anywhere an expression is allowed, and are used to return information from the database.

See the Using Transact-SQL Functions in Queries in the Transact-SQL Users Guide for detailed information about how to use these functions.

See XML Services for detailed information about the XML functions: xmlextract, xmlparse, xmlrepresentation, xmltable, xmltest, and xmlvalidate.

The permission checks for Transact-SQL functions differ based on your granular permissions settings. See the Security Administration Guide for more information on granular permissions.

• abs
  Returns the absolute value of an expression.
• acos
  Returns the angle (in radians) of the specified cosine.
• allocinfo
  Returns a list of allocation pages that are stored in an object allocation map (OAM) page.
• ascii
  Returns the ASCII code for the first character in an expression.
• asehostname
  Returns the physical or virtual host on which the SAP ASE server is running.
• asin
  Returns the angle (in radians) of the specified sine.
• atan
  Returns the angle (in radians) of a tangent with the specified value.
• atn2
  Returns the angle (in radians) of the specified sine and cosine.
• avg
  Calculates the numeric average of all (distinct) values.
• audit_event_name
  Returns a description of an audit event.
• authmech
  Determines what authentication mechanism is used by a specified logged in server process ID.
• biginttohex
  Returns the platform-independent 8 byte hexadecimal equivalent of the specified integer.
• bintostr
  Converts a sequence of hexadecimal digits to a string of its equivalent alphanumeric characters or varbinary data.
• cache_usage
  Returns cache usage as a percentage of all objects in the cache to which the table belongs.
• case
  case expression simplifies standard SQL expressions by allowing you to express a search condition using a when...then construct instead of an if statement. It supports conditional SQL expressions; can be used anywhere a value expression can be used.
• cast
  Converts the specified value to another datatype.
• ceiling
  Returns the smallest integer greater than or equal to the specified value.
• char
  Converts a single-byte integer value to a character value (char is usually used as the inverse of ascii), returning the character equivalent of an integer.
• char_length
  Returns the number of characters in an expression.
• charindex
  Returns an integer representing the starting position of an expression.
• coalesce
  Supports conditional SQL expressions; can be used anywhere a value expression can be used; alternative for a case expression. coalesce expression simplifies standard SQL expressions by allowing you to express a search condition as a simple comparison instead of using a when...then construct.
• col_length
  Returns the defined length of a column.
• col_name
  Returns the name of the column where the table and column IDs are specified, and can be up to 255 bytes in length.
• compare
  Allows you to directly compare two character strings based on alternate collation rules.
• convert
  Converts the specified value to another datatype or a different datetime display format.
• cos
  Returns the cosine of the angle specified in radians.
• cot
  Returns the cotangent of the angle specified in radians.
• count
  Returns the number of (distinct) non-null values, or the number of selected rows as an integer.
• count_big
  Returns the number of (distinct) non-null values, or the number of selected rows as a bigint.
• create_locator
  Explicitly creates a locator for a specified LOB then returns the locator.
• current_bigdatetime
  Finds the current date as it exists on the server, and 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
  Finds the current date as it exists on the server, and 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_date
  Finds and returns the current date as it exists on the server.
• current_time
  Finds and returns the current time as it exists on the server.
• curunreservedpgs
  Displays the number of free pages in the specified disk piece.
• data_pages
  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.
• datachange
  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.
• datalength
  Returns the actual length, in bytes, of the specified column or string.
• dateadd
  Adds an interval to a specified date or time.
• datediff
  Calculates the number of date parts between two specified dates or times.
• datename
  Returns the specified datepart of the specified date or time as a character string.
• datepart
  Returns the integer value of the specified part of a date expression
• day
  Returns an integer that represents the day in the datepart of a specified date.
• db_attr
  Returns the durability, dml_logging, and template settings, and compression level for the specified database.
• db_id
  Displays the ID number of the specified database.
• db_instanceid
  (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_name
  Displays the name of the database with the specified ID number.
• db_recovery_status
  (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.
• dbencryption_status
  Reports database encryption/decryption status and progress.
• defrag_status
  Returns metrics of any defragmentation operation that is started or ongoing on the named object or partition.
• degrees
  Converts the size of the angle from degrees to radians.
• derived_stat
  Returns derived statistics for the specified object and index.
• difference
  Returns the difference between two soundex values.
• dol_downgrade_check
  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.
• exp
  Calculates the value that results from raising a constant to the specified power, and returns the exponential value of the specified value.
• floor
  Returns the largest integer that is less than or equal to the specified value.
• get_appcontext
  Returns the value of the attribute in a specified context. get_appcontext is provided by the Application Context Facility (ACF).
• get_internal_date
  Returns the current date and time from the internal clock maintained by the SAP ASE server.
• getdate
  Returns the current system date and time.
• getutcdate
  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.
• has_role
  Returns information about whether an invoking user has been granted, and has activated, the specified role.
• hash
  Produces a fixed-length hash value expression.
• hashbytes
  Produces a fixed-length, hash value expression.
• hextobigint
  Returns the platform-independent bigint value equivalent of a hexadecimal string
• hextoint
  Returns the platform-independent integer equivalent of a hexadecimal string.
• host_id
  Returns the client computer’s operating system process ID for the current SAP ASE client (not the server process).
• host_name
  Displays the current host computer name of the client process (not the server process).
• instance_id
  (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.
• identity_burn_max
  Tracks the identity burn max value for a given table. This function returns only the value; it does not perform an update.
• index_col
  Displays the name of the indexed column in the specified table or view to a maximum of 255 bytes in length.
• index_colorder
  Returns the column order.
• index_name
  Returns an index name, when you provide the index ID, the database ID, and the object on which the index is defined.
• inttohex
  Returns the platform-independent hexadecimal equivalent of the specified integer, without a “0x” prefix.
• isdate
  Determines whether an input expression is a valid datetime value.
• 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
  Determines whether a particular security service is active during the session.
• is_singleusermode
  Determines whether the SAP ASE server is running in single-user mode.
• isnull
  Substitutes the value specified in expression2 when expression1 evaluates to NULL.
• isnumeric
  Determines if an expression is a valid numeric datatype.
• instance_name
  (Cluster environments only) Returns the name for the SAP ASE with an ID that you provide, or the name of the SAP ASE from which it is issued if you do not provide a value for id.
• lc_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_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.
• lct_admin
  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.
• left
  Returns a specified number of characters on the left end of a character string.
• len
  Returns the number of characters, not the number of bytes, of a specified string expression, excluding trailing blanks.
• license_enabled
  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.
• list_appcontext
  Lists all the attributes of all the contexts in the current session. list_appcontext is provided by the ACF.
• locator_literal
  Identifies a binary value as a locator literal.
• locator_valid
  Determines whether a LOB locator is valid.
• lockscheme
  Returns the locking scheme of the specified object as a string.
• log
  Calculates the natural logarithm of the specified number.
• log10
  Calculates the base 10 logarithm of the specified number.
• loginfo
  Returns information about a transaction log.
• lower
  Converts uppercase characters to lowercase, returning a character value.
• lprofile_id
  Returns the login profile ID of the specified login profile name, or the login profile ID of the login profile associated with the current login or the specified login name.
• lprofile_name
  Returns the login profile name of the specified login profile ID, or the login profile name of the login profile associated with the current login or the specified login suid.
• ltrim
  Removes leading blanks from the character expression. Only values equivalent to the space character in the current character set are removed.
• max
  Returns the maximum value in a column or expression.
• migrate_instance_id
  If issued in the context of a migrated task, migrate_instance_id returns the instance ID of the instance from which the caller migrated. If issued in the context of a nonmigrated task, migrate_instance_id returns the ID of the current instance.
• min
  Returns the lowest value in a column.
• month
  Returns an integer that represents the month in the datepart of a specified date.
• mut_excl_roles
  Returns information about the mutual exclusivity between two roles.
• newid
  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).
• next_identity
  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_attr
  Reports the table’s current logging mode, depending on the session, table and database-wide settings.
• object_id
  Returns the object ID of the specified object.
• object_name
  Returns the name of the object with the object ID you specify; up to 255 bytes in length.
• object_owner_id
  Returns an object’s owner ID.
• pagesize
  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
  Returns the explicit name of a new partition, partition_name returns the partition name of the specified data or index partition id.
• partition_object_id
  Displays the object ID for a specified partition ID and database ID.
• password_random
  Generates a pseudorandom password that satisfies the global password complexity checks defined on the SAP ASE server. “Pseudorandom” indicates that the SAP ASE server is simulating random-like numbers, since no computer generates truly random numbers.
• patindex
  Returns the starting position of the first occurrence of a specified pattern.
• pi
  Returns the constant value 3.1415926535897936.
• power
  Returns the value that results from raising the specified number to a given power. power, a mathematical function, returns the value of value raised to the power power. Results are of the same type as value.
• proc_role
  Returns information about whether the user has been granted a specified role.
• pssinfo
  Returns information from the SAP ASE process status structure (pss).
• radians
  Converts degrees to radians. Returns the size, in radians, of an angle with the specified number of degrees.
• rand
  Returns a random float value between 0 and 1 using the specified (optional) integer as a seed value.
• rand2
  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. Unlike rand, it is computed for each returned row when it is used in the select list.
• replicate
  Returns a string with the same datatype as char_expr or uchar_expr containing the same expression repeated the specified number of times or as many times as fits into 16K, whichever is less.
• reserve_identity
  reserve_identity allows a process to reserve a block of identity values for use by that process.
• reserved_pages
  Reports the number of pages reserved for a database, object, or index. The result includes pages used for internal structures.
• return_lob
  Dereferences a locator, and returns the LOB referenced by that locator.
• reverse
  Returns the specified string with characters listed in reverse order.
• right
  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.
• rm_appcontext
  Removes a specific application context, or all application contexts. rm_appcontext is provided by the Application Context Facility (ACF).
• role_contain
  Determines whether a specified role is contained within another specified role.
• role_id
  Returns the role ID of the specified role name.
• role_name
  Returns the role name of the specified role ID.
• round
  Returns the value of the specified number, rounded to the specified number of decimal places.
• row_count
  Returns an estimate of the number of rows in the specified table.
• rtrim
  Trims the specified expression of trailing blanks.
• sdc_intempdbconfig
  (Cluster environments only) Returns 1 if the system is currently in temporary database configuration mode; if not, returns 0.
• set_appcontext
  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.
• setdata
  Overwrites some or all of a large object (LOB).
• shrinkdb_status
  Determines the status of a shrink operation.
• show_cached_plan_in_xml
  Displays, in XML, the executing query plan for queries in the statement cache.
• show_cached_text
  Displays the SQL text of a cached statement.
• show_cached_text_long
  Displays the SQL text for cached statements longer than 16K.
• show_condensed_text
  Returns the unified SQL text for cached statements.
• show_dynamic_params_in_xml
  Returns parameter information for a dynamic SQL query (a prepared statement) in XML format.
• show_plan
  Retrieves the query plan for a specified server process (the target process) and a SQL statement. This function is called several times by sp_showplan because a built-in function can return just one value per call, but sp_showplan must return several values to the client.
• show_role
  Displays the currently active system-defined roles of the current login.
• show_sec_services
  Lists the security services that are active for the session.
• sign
  Returns the sign (1 for positive, 0, or -1 for negative) of the specified value.
• sin
  Returns the sine of the angle-specified in radians.
• sortkey
  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.
• soundex
  Returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double-byte Roman letters.
• space
  Returns a string consisting of the specified number of single-byte spaces.
• spaceusage
  Returns metrics for space use in SAP ASE as a comma-separated string.
• spid_instance_id
  (Cluster environments only) Returns the instance ID on which the specified process ID (spid) is running.
• square
  Calculates the square of a specified value expressed as a float.
• sqrt
  Calculates the square root of the specified number.
• stddev
  Computes the standard deviation of a sample consisting of a numeric expression, as a double.
• stdev
  Computes the standard deviation of a sample consisting of a numeric expression, as a double.
• stdevp
  Computes the standard deviation of a population consisting of a numeric expression, as a double.
• stddev_pop
  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.
• stddev_samp
  Computes the standard deviation of a sample consisting of a numeric expression as a double. stdev and stddev are aliases for stddev_samp, and use the same syntax.
• str
  Returns the character equivalent of the specified number, and pads the output with a character or numeric to the specified length.
• str_replace
  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).
• strtobin
  Converts a sequence of alphanumeric characters to their equivalent hexadecimal digits.
• stuff
  Returns the string formed by deleting a specified number of characters from one string and replacing them with another string.
• substring
  Returns the string formed by extracting the specified number of characters from another string.
• sum
  Returns the total of the values.
• suser_id
  Returns the server user’s ID number from the syslogins table.
• suser_name
  Returns the name of the current server user, or the user whose server ID is specified.
• syb_quit
  Terminates the connection.
• syb_sendmsg
  (UNIX only) Sends a message to a User Datagram Protocol (UDP) port.
• sys_tempdbid
  (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.
• tan
  Calculates the tangent of the angle, specified in radians.
• tempdb_id
  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.
• textptr
  Returns a pointer to the first page of a text, image, or unitext column.
• textvalid
  Returns 1 if the pointer to the specified text, unitext, in-row, and off-row LOB columns is valid; 0 if it is not.
• to_unichar
  Returns a unichar expression having the value of the specified integer expression.
• tran_dumpable_status
  Returns a true/false indication of whether dump transaction is allowed.
• tsequal
  Compares timestamp values to prevent update on a row that has been modified since it was selected for browsing.
• uhighsurr
  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.
• ulowsurr
  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().
• upper
  Converts specified lowercase string to the uppercase equivalent.
• uscalar
  Returns the Unicode scalar value for the first Unicode character in an expression.
• used_pages
  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 SAP ASE earlier than 15.0.
• user
  Returns the name of the current user.
• user_id
  Returns the ID number of the specified user or of the current user in the database.
• user_name
  Returns the name within the database of the specified user or of the current user.
• valid_name
  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_user
  Returns 1 if the specified ID is a valid user or alias in at least one database.
• var
  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_pop
  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_samp
  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.
• variance
  Computes the statistical variance of a sample consisting of a numeric expression, as a double, and returns the variance of a set of numbers.
• varp
  Computes the statistical variance of a population consisting of a numeric expression, as a double.
• workload_metric
  (Cluster environments only) Queries the current workload metric for the instance you specify, or updates the metric for the instance you specify.
• xa_bqual
  Returns the binary version of the bqual component of an ASCII XA transaction ID.
• xa_gtrid
  Returns the binary version of the gtrid component of an ASCII XA transaction ID.
• xact_connmigrate_check
  (Cluster environments only) Determines whether or not a connection can process an external transaction.
• xact_owner_instance
  (Cluster environments only) Returns the instance ID on which the distributed transaction is running.
• xmlextract
  Applies an XML query expression to an XML document and returns the specified result. Information can be returned with or without the XML tags.
• xmlparse
  Parses an XML document passed as a parameter, and returns an image (default), binary, or varbinary value that contains a parsed form of the document.
• xmlrepresentation
  Examines the image parameter of an expression, and returns an integer value that indicates whether the parameter contains parsed XML data or another sort of image data.
• xmltable
  Extracts data from an XML document and returns it as a SQL table.
• xmltest
  Is a SQL predicate that evaluates an XML query expression, which can reference the XML document parameter, and returns a Boolean result. xmltest resembles a SQL like predicate.
• xmlvalidate
  Validates an XML document.
• year
  Returns an integer that represents the year in the datepart of a specified date.