|
|
Every RDBMS maintains an ever-expanding list of the SQL functions specific to its own version. Some of the most useful functions were covered in Chapter 10 of this book. This appendix provides a comprehensive list of the functions available for each RDBMS in its current version: Oracle 9i, IBM DB2 UDB 8.1, and Microsoft SQL Server 2000.
All the functions are grouped in Tables G-1 through G-24 similarly to the way they are organized in each corresponding vendor's documentation. Oracle 9i allows overloading of the functions, meaning that some functions can be used in more than one way depending on the data type and number of arguments; because of that such functions are listed in several categories. IBM rarely provides overloaded functions, and Microsoft stands somewhere between Oracle and IBM in this regard.
Certain functions require appropriate privileges to be run, as well as specific output result holders. We've omitted the arguments of the functions from the tables, because the purpose of the appendix is to show what is available, without going into too much detail. Refer to each vendor's documentation for more detailed syntax and explanations.
Function Name | Brief Description |
---|---|
ABS | Returns the absolute value of n. |
ACOS | Returns the arc cosine of n. |
ASIN | Returns the arc sine of n. |
ATAN | Returns the arc tangent of n. |
ATAN2 | Returns the arc tangent of n and m. |
BITAND | Returns an integer. |
CEIL | Returns the smallest integer greater than or equal to n. |
COS | Returns the cosine of n. |
COSH | Returns the hyperbolic cosine of n. |
EXP | Returns e raised to the nth power, where e=2.71828183. |
FLOOR | Returns the largest integer equal to or less than n. |
LN | Returns the natural logarithm of n, where n is greater than 0. |
LOG | Returns the logarithm, base m, of n. |
MOD | Returns the remainder of m divided by n. Returns m if n is 0. |
POWER | Returns m raised to the nth power. |
SIGN | If n<0, SIGN returns –1. If n=0, the function returns 0. If n>0, SIGN returns 1. |
SINH | Returns the hyperbolic sine of n. |
SQRT | Returns the square root of n. |
TAN | Returns the tangent of n. |
TANH | Returns the hyperbolic tangent of n. |
TRUNC | Returns a value truncated to m decimal places. If m is omitted, n is truncated to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point. |
WIDTH_BUCKET | Lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. |
Function Name | Brief Description |
---|---|
CHR | Returns the character having the binary equivalent to n. |
CONCAT | Returns char1 concatenated with char2. |
INITCAP | Returns char, the first letter in uppercase, all other letters in lowercase. |
LOWER | Returns char, all letters in lowercase. |
LPAD | Returns char1, left-padded to length n with the sequence of characters in char2; if char1 is longer than n, this function returns the portion of char1 that fits in n. |
LTRIM | Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. |
NLS_INITCAP | Returns char, with the first letter of each word in uppercase, all other letters in lowercase. |
NLS_LOWER | Returns char, with all letters lowercase. |
NLSSORT | Returns the string of bytes used to sort char. |
NLS_UPPER | Returns char, with all letters uppercase. |
REPLACE | Returns char with every occurrence of search_string replaced with replacement_string. |
RPAD | Returns char1, right-padded to length n with char2 replicated as many times as necessary. |
RTRIM | Returns char, with all rightmost characters that appear in set removed. |
SOUNDEX | Returns a character string containing the phonetic representation of char. |
SUBSTR | Returns a portion of string, beginning at a specified character position that is substring_length characters long. SUBSTR calculates lengths using characters defined by the input character set. |
SUBSTRB | Same as STRING, except SUBSTRB uses bytes instead of characters. |
SUBSTRC | Same as STRING, except SUBSTRC uses Unicode-complete characters. |
SUBSTR2 | Same as STRING, except SUBSTR2 uses UCS2 codepoints. |
SUBSTR4 | Same as STRING, except SUBSTR4 uses UCS4 codepoints. |
TRANSLATE | Returns char with all occurrences of each character in from_string, replaced by its corresponding character in to_string. |
TREAT | Returns a declared type of expression. |
UPPER | Returns char, with all letters uppercase. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is the same data type as char. |
Function Name | Brief Description |
---|---|
ADD_MONTHS | Returns the date d plus n months. |
CURRENT_DATE | Returns the current date and time in the session's time zone, with a value in Gregorian calendar format with the data type DATE. |
CURRENT_TIMESTAMP | Returns the current date and time in the session's time zone, with the value of data type TIMESTAMP WITH TIME ZONE. |
DBTIMEZONE | Returns the value of the database time zone. |
EXTRACT | Returns the value of a specified datetime field from a datetime or interval value expression. |
FROM_TZ | Converts a timestamp value in a time zone to a TIMESTAMP WITH TIME ZONE value. time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format. |
LAST_DAY | Returns the date of the last day of the month that contains this date. |
LOCALTIMESTAMP | Returns the current date and time in the session's time zone in a value of the data type TIMESTAMP. |
MONTH_BETWEEN | Returns the number of months between dates date1 and date2. |
NEW_TIME | Returns the date and time in time zone zone2 when the date and time in time zone zone1 are the date argument. |
NEXT_DAY | Returns the date of the first weekday named by char that is later than the date argument. |
NUMTODSINTERVAL | Converts n to an INTERVAL DAY TO SECOND literal. n can be a number or an expression resolving to a number. |
NUMTOYMINTERVAL | Converts n to an INTERNAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number. |
ROUND | Truncates time portion of the date. |
SESSIONTIMEZONE | Returns the value of the current session's time zone. |
SYS_EXTRACT_UTC | Extracts the UTC (Coordinated Universal Time) from a datetime with time zone displacement. |
SYSTEMSTAMP | Returns the system date, including fractional seconds and the time zone of the database. |
SYSDATE | Returns the current date and time. |
TO_DSINTERVAL | Converts a character string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND TYPE. |
TO_TIMESTAMP | Converts char of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the TIMESTAMP data type. |
TO_TIMSTAMP_TZ | Converts char of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP WITH TIME ZONE. |
TO_YMINTERVAL | Converts a character string of the CHAR, VARCHAR2, NVARCHAR, or NVARCHAR2 data type to an INTERVALYEAR TO MONTH type. |
TRUNC | Returns the date with the time portion of the day truncated to the unit specified by the format model format. If you omit format, date is truncated to the nearest day. |
TZ_OFFSET | Returns the time zone offset corresponding to the value entered based on the date the statement is executed. You can enter a valid time zone name, a time zone offset from UTC (which simply returns itself), or the keyword SESSIONTIMEZONE or DBTIMEZONE. For a listing of valid values, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view. |
Function Name | Brief Description |
---|---|
ASCIISTR | Returns an ASCII string in the database character set. The value returned contains only characters that appear in SQL, plus the forward slash (/). |
BIN_TO_NUM | Converts a bit vector to its equivalent number. Each argument in this function represents a bit in the bit vector. Each expression must evaluate to 0 or 1. This function returns NUMBER. |
CAST | Converts one built-in data type or collection-type value into another built-in data type or collection-type value. |
CHARTOROWID | Converts a value from the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to the ROWID data type. |
COMPOSE | Returns a Unicode string in its fully normalized form in the same character set as"the input. |
CONVERT | Converts a character string from one character set to another. The data type of the returned value is VARCHAR2. |
DECOMPOSE | Returns a Unicode string after canonical decomposition in the same character set as the input. |
HEXTORAW | Converts char containing hexadecimal digits in the CHAR, VARCHAR2, NVARCHAR, or NVARCHAR2 character set to a raw value. |
NUMTODSINTERVAL | Converts n to an INTERVAL DAY TO SECOND LITERAL. n can be a number or an expression resolving to a number. |
NUMTOYMINTERVAL | Converts number n to an INTERVAL YEAR TO MONTH literal. n can be a number or an expression resolving to a number. |
RAWTOHEX | Converts raw data type to a character value containing its hexadecimal equivalent. |
RAWTONHEX | Converts raw data type to an NVARCHAR2 character value containing its hexadecimal equivalent. |
ROWIDTOCHAR | Converts a rowid value to a VARCHAR2 data type. The result of this conversion is always 18 characters long. |
ROWIDTONCHAR | Converts a rowid value to a NVARCHAR2 data type. The result of this conversion is always 18 characters long. |
TO_CHAR | Converts the NCHAR, NVARCHAR2, CLOB, or NCLOB data type to the database character set. |
TO_CHAR | Converts date of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH A LOCAL TIME ZONE data type to a value of the VARCHAR2 data type in the format specified by the date format. |
TO_CHAR | Converts n of the NUMBER data type to a value of the VARCHAR2 data type. |
TO_CLOB | Converts NCLOB values in an LOB column or other character string to CLOB values. |
TO_DATE | Converts char of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2, data type to a value of the DATE data type. |
TO_DSINTERVAL | Converts a character string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL DAY TO SECOND TYPE. |
TO_LOB | Converts LONG or LONG RAW values in the column long_column to LOB values. |
TO_MULTI_BYTE | Returns char with all of its single-byte characters converted to their corresponding multibyte characters. The value returned is in the same data type as char. |
Function Name | Brief Description |
TO_NCHAR | Converts a character string, CLOB, NCLOB, from the database character set to the national character set. |
TO_NCHAR | Converts a character string of the DATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE ......etc. data type from the database character set to the national character set. |
TO_NCHAR | Converts a number to a string in the NVARCHAR2 character set. |
TO_NCLOB | Converts CLOB values in a LOB column or other character string to NCLOB values. |
TO_NUMBER | Converts char to a value of the NUMBER data type. |
TO_SINGLE_BYTE | Returns char with all of its multibyte characters converted to their corresponding single-byte characters. |
TO_YMINTERVAL | Converts a character string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH data type, where char is the character string to be converted. |
TRANSLATE ... USING | Converts text into the character set specified for conversions between the database character set and the national character set. |
UNISTR | Takes as its argument a string in any character set and returns it in Unicode in the database Unicode character set. |
Function Name | Brief Description |
---|---|
BFILENAME | Returns a BFILE locator that is associated with a physical LOB binary file on the server's file system. |
COALESCE | Returns the first non-NULL expression in the expression list. |
DECODE | Compares an expression to each search value one by one. If expression is equal to a search, Oracle returns the corresponding result. If no match is found, returns default, or, if default is omitted, returns NULL. |
DUMP | Returns a VARCHAR2 value containing the data type codelength in bytes, and internal representation of expression. |
EMPTY_BLOB | Returns an empty LOB locator that can be used to initialize an LOB variable or, an INSERT or UPDATE statement. |
EMPTY_CLOB | Returns an empty LOB locator that can be used to initialize an LOB variable or, an INSERT or UPDATE statement. |
EXISTSNODE | Determines whether traversal of the document using the path results in any nodes. It takes as arguments the XMLType instance containing an XML document and a VARCHAR2 string designating a path. |
EXTRACT | Returns the value of specified datetime field from a datetime or interval value expression. |
GREATEST | Returns the greatest value on the list of expressions. |
LEAST | Returns the least value on the list of expressions. |
NLS_CHARSET_DECL_LEN | Returns the declaration width (in number of characters) of an NCHAR column. |
NLS_CHARSET_ID | Returns the character set ID number corresponding to the character set name text. |
NLS_CHARSET_NAME | Returns the name of the character set corresponding to the ID number. |
NULLIF | Returns NULL, if expression1 and expression2 are equal. If they are not equal, the function returns expression1. |
NVL | If expression1 is NULL, NVL returns expression2. If not NULL, NVL returns expression1. |
NVL2 | If expression1 is not NULL, NVL2 returns expression2, If expression2 is NULL, NVL2 returns expression3. |
SYS_CONNECT_BY_PATH | Returns the path of a column value from root to node, with column values separated by char for each row returned by the CONNECT BY condition. |
SYS_CONTEXT | Returns the value of the parameter associated with the context namespace. |
SYS_DBURIGEN | Takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URL of data type DBUriType to a particular column or row object. You can then use the URL to retrieve an XML document from the database. |
SYS_EXTRACT_UTC | Extracts the UTC (Coordinated Universal Time — formerly Greenwich Mean Time) from a datetime with time zone displacement. Returns datetime with time zone displacement. |
SYS_GUID | Returns a globally unique identifier (RAW value) made up of 16 bytes. |
SYS_TYPEID | Returns the typeid of the most specific type of the operand. |
Function Name | Brief Description |
SYS_XMLAGG | Aggregates all of the XML documents or fragments represented by an expression and produces a single XML document. |
SYS_XMLGEN | Takes an expression that evaluates to a particular row and column of the database, and returns an instance of type SYS.XMLType containing an XML document. |
UID | Returns an integer that uniquely identifies the session user (the user who logged on). |
USER | Returns the name of the session user (the user who logged on) with the data type VARCHAR2. |
USERENV | Returns information from the VARCHAR2 data type about the current session. USERENV is a legacy function that is retained for backward compatibility; use the SYS_CONTEXT function instead. |
VSIZE | VSIZE returns the number of bytes in the internal representation of expression. If expression is NULL, this function returns NULL. |
Function Name | Brief Description |
---|---|
AVG | Returns the average value of an expression, could be used with GROUP BY. |
CORR | Returns the coefficient of correlation of a set of number pairs. |
COUNT | Returns the number of rows in the query. |
COVAR_POP | Returns the population covariance of a set of number pairs. |
COVAR_SAMP | Returns the sample covariance of a set of number pairs. |
CUME_DIST | As an aggregate function, calculates, for a hypothetical row R identified by the arguments of the function and a corresponding sort specification, the relative position of row R among the rows in the aggregation group. |
DENSE_RANK | As an aggregate function, calculates the dense rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. |
FIRST | Returns the value from the first row of a sorted group. |
GROUP_ID | Distinguishes duplicate groups resulting from a GROUP BY specification. It is therefore useful in filtering out duplicate groupings from the query result. It returns a NUMBER to uniquely identify duplicate groups. |
GROUPING | Distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce superaggregate rows where the set of all values is represented by NULL. Using the GROUPING function, you can distinguish a NULL representing the set of all values in a superaggregate row from a NULL in a regular row. |
GROUPING_ID | Returns a number corresponding to the GROUPING bit vector associated with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function. |
LAST | Returns the value from the last row of a sorted group. |
MAX | Returns the maximum value of an expression. |
MIN | Returns the minimum value of an expression. |
PERCENTILE_CONT | Is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation. |
PERCENTILE_DISC | Is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation. |
PERCENT_RANK | Is similar to the CUME_DIST (cumulative distribution) function. The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0. |
RANK | As an aggregate function, calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. |
STDDEV | Returns the sample standard deviation of an expression, a set of numbers; differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns a NULL. |
STDDEV_POP | Computes the population standard deviation and returns the square root of the population variance. |
STDDEV_SAMP | Computes the cumulative sample standard deviation and returns the square root of the sample variance. |
SUM | Returns the sum of the values of an expression. |
Function Name | Brief Description |
VAR_POP | Returns the population variance of a set of numbers after discarding the NULLs in this set. |
VAR_SAMP | Returns the sample variance of a set of numbers after discarding the NULLs in this set. |
VARIANCE | Returns the variance of an expression. Can be used as an aggregate or analytic function. |
Function Name | Brief Description |
---|---|
AVG | Returns the average the value of an expression, could return multiple rows within group |
CORR | Returns the coefficient of correlation of a set of number pairs. |
COVAR_POP | Returns the population covariance of a set of number pairs. |
COVAR_SAMP | Returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function. |
COUNT | Returns the number of rows in the query. |
CUME_DIST | Calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. |
DENSE_RANK | Computes the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. |
FIRST_VALUE | Returns the first value in an ordered set of values. |
LAG | Provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position. |
LAST | Returns the value from the last row of a sorted group. |
LAST_VALUE | Returns the last value in an ordered set of values. |
LEAD | Provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LEAD provides access to a row at a given physical offset beyond that position. |
MAX | Returns the maximum value of an expression. |
MIN | Returns the minimum value of an expression. |
NTILE | Divides an ordered dataset into a number of buckets indicated by an expression and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expression, and expression must resolve to a positive constant for each partition. |
PERCENT_RANK | Is similar to the CUME_DIST (cumulative distribution) function. The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0. |
PERCENTILE_COUNT | Is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation. |
PERCENTILE_DISC | Is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation. |
RANK | Calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank; the ranks may not be consecutive numbers. |
RATIO_TO_REPORT | Computes the ratio of a value to the sum of a set of values. If expression evaluates to NULL, the ratio-to-report value also evaluates to NULL. |
ROW_NUMBER | Assigns a unique number to each row to which it is applied (either each row in the partition or each row returned), in the ordered sequence of rows specified in the order_by_clause, beginning with 1. |
REGR_SLOPE | The linear regression functions that follow fit an ordinary-least-squares regression line to a set of number pairs: |
STDDEV | Returns the sample standard deviation of an expression, a set of numbers; differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns a NULL. |
STDDEV_POP | Computes the population standard deviation and returns the square root of the population variance. |
STDDEV_SAMP | Computes the cumulative sample standard deviation and returns the square root of the sample variance. |
SUM | Returns the sum of the values of an expression. |
VAR_POP | Returns the population variance of a set of numbers after discarding the NULLs in this set. |
VAR_SAMP | Returns the sample variance of a set of numbers after discarding the NULLs in this set. |
VARIANCE | Returns the variance of an expression. Can be used as an aggregate or analytic function. |
Function Name | Brief Description |
---|---|
DEREF | Returns the object reference of an argument expression, where expression must return a REF to an object. |
MAKE_REF | Creates a REF to a row of an object view or a row in an object table whose object identifier is primary-key based. |
REF | Returns a REF value for the object instance that is bound to the variable or row. |
REFTOHEX | Converts an argument expression to a character value containing its hexadecimal equivalent. The expression must return a REF. |
VALUE | Takes as its argument a correlation variable (table alias) associated with a row of an object table and returns object instances stored in the object table. The type of the object instance is the same type as the object table. |
Function Name | Brief Description |
---|---|
ABS[VAL] | Returns the absolute value of a number n. |
ACOS | Returns the arccosine of the argument as an angle expressed in radians. |
ASCII | Returns the ASCII code of the first character of a string. |
ASIN | Returns the arcsine of the argument as an angle expressed in radians. |
ATAN | Returns the arctangent of the argument as an angle expressed in radians. |
ATAN2 | Returns the arctangent of x and y coordinates as an angle expressed in radians. The x and y coordinates are specified by the first and second arguments, respectively. |
BIGINT | Returns a 64-bit integer representation of a number or character string in the form of an integer constant. |
BLOB | Returns a BLOB representation of a string of any type. |
CEIL[ING] | Returns the smallest integer that is greater than or equal to n. |
CHAR | Converts argument expression into a character string. |
CHR | Returns a character for the ASCII code. |
CLOB | Returns a CLOB representation of a character string type. |
COALESCE | Returns the first argument on the list that is not NULL. |
CONCAT | Returns result of concatenation of two strings. |
COS | Returns the cosine of the argument, where the argument is an angle expressed in radians. |
COT | Returns the cotangent of the argument, where the argument is an angle expressed in radians. |
DATE | Returns a date from a value. |
DAY | Returns the day part of a value. |
DAYNAME | Returns a mixed-case character string containing the name of the day for the day portion of the argument based on the locale where the database was started. |
DAYOFWEEK | Returns the day of the week in the argument as an integer value in the range of 1–7, where 1 represents Sunday. |
DAYOFWEEK_ISO | Returns the day of the week in the argument as an integer value in the range of 1–7, where 1 represents Monday. |
DAYOFYEAR | Returns the day of the year in the argument as an integer value in the range of 1–366. |
DAYS | Returns an integer representation of a date. |
DBCLOB | Returns a DBCLOB representation of a graphic string type. |
DEC[IMAL] | Returns a decimal representation of a number, a character string representation of a decimal number, or a character string representation of an integer number. |
DECRYPT_BIN | Returns a value that is the result of decrypting encrypted data. |
DECRYPT_CHAR | Returns a value that is the result of decrypting encrypted data. |
DEGREES | Returns the number of degrees converted from the argument expressed in radians. |
Function Name | Brief Description |
DEREF | Returns an instance of the target type of the argument. |
DIFFERENCE | Returns a value from 0 to 4 representing the difference between the sounds of two strings based on applying the SOUNDEX function to the strings. A value of 4 is the best possible sound match. |
DIGITS | Returns a character-string representation of a number. |
DLCOMMENT | Returns the comment value, if it exists, from a DATALINK value. |
DLLINKTYPE | Returns the linktype value from a DATALINK value. |
DLURLCOMPLETE | Returns the data location attribute from a DATALINK value with a linktype of URL. |
DLURLPATH | Returns the path and file name necessary to access a file within a given server from a DATALINK value with a linktype of URL. |
DLURLPATHONLY | Returns the path necessary to access a file within a given server from a DATALINK value with a linktype of URL. |
DLURLSCHEME | Returns the scheme from a DATALINK value with a linktype of URL. |
DLURLSERVER | Returns the file server from a DATALINK value with a linktype of URL. |
DLVALUE | Returns a DATALINK value. |
DOUBLE | Returns a floating-point number corresponding to a number if the argument is a numeric expression character string, or a representation of a number if the argument is a string expression. |
ENCRYPT | Returns a value that is the result of encrypting a data-string expression. |
EVENT_MON_STATE | Returns the current state of an event monitor. |
EXP | Returns the exponential value of n. |
FLOAT | Returns a floating-point representation of a number. |
FLOOR | Returns the largest integer less than or equal to n. |
GETHINT | Returns will return the password hint if one is found in the encrypted data. |
GENERATE_UNIQUE | Returns a bit-data character string 13 bytes long (CHAR(13) FOR BIT DATA) that is unique when compared to any other execution of the same function. |
GRAPHIC | Returns a GRAPHIC representation of a graphic string type. |
HEX | Returns a hexadecimal representation of a value as a character string. |
HOUR | Returns the hour part of a value. |
IDENTITY_VAL_LOCAL | Returns the most recently assigned value for an identity column, where the assignment occurred as a result of a single row INSERT statement using a VALUES clause. |
INSERT | Returns a string where expression3 bytes have been deleted from expression1 beginning at expression2, and where expression4 has been inserted into expression1 beginning at expression2. |
INTEGER | Returns an integer representation of a number or character string in the form of an integer constant. |
JULIAN_DAY | Returns an integer value representing the number of days from January 1, 4712 B.C. (the start of the Julian date calendar) to the date value specified in the argument. |
LOWER | Converts all characters in a string to lowercase. |
Function Name | Brief Description |
LCASE | Converts all characters in a string to lowercase. |
LEFT | Returns n number of characters starting from the left. |
LENGTH | Returns the number of characters in a string. |
LN | Returns the natural logarithm of the argument. |
LOCATE | Returns the position of an occurrence of a substring within the string. |
LOG | Returns the natural logarithm of the argument (same as LN). |
LOG10 | Returns the base 10 logarithm of the argument. |
LONG_VARCHAR | Returns a LONG VARCHAR representation of a character string data type. |
LONG_VARGRAPHIC | Returns a LONG VARGRAPHIC representation of a double-byte character string. |
LTRIM | Trims leading spaces off the string. |
MICROSECOND | Returns the microsecond part of a value. |
MIDNIGHT_SECONDS | Returns an integer value, which represents the number of seconds between midnight and the time value specified in the argument. |
MINUTE | Returns the minute part of a value. |
MOD | Returns the remainder of n divided by m. |
MONTH | Returns the month part of a value. |
MONTHNAME | Returns a mixed-case character string containing the name of month for the month portion of the argument, based on the locale where the database was started. |
MQPUBLISH | Returns published data to MQSeries. |
MQREAD | Returns a message from the MQSeries location specified by receive-service. The read is non-destructive — i.e. the message itself remains in the queue. |
MQRECEIVE | Same as above, but the message in the queue is destroyed. |
MQSEND | Sends the data contained in message data to the MQSeries location specified by send-service, using the quality of service policy defined by service-policy. |
MQSUBSCRIBE | Used to register interest in MQSeries messages published on a specified topic. |
MQUNSUBSCRIBE | Used to unregister an existing message subscription. |
MULTIPLY_ALT | Returns the product of the two arguments as a decimal value. |
NODENUMBER | Returns the partition number of the row. |
NULLIF | Returns a NULL value if the arguments are equal, otherwise it returns the value of the first argument. |
PARTITION | Returns the partitioning map index of the row obtained by applying the partitioning function on the partitioning key value of the row. |
POSSTR | Returns the position of an occurrence of a substring within the string. The POSSTR test is case-sensitive |
POWER | Returns value of m raised to the nth power. |
QUARTER | Returns an integer value in the range of 1 to 4, which represents the quarter of the year for the date specified in the argument. |
RADIANS | Returns the number of radians converted from an argument, which is expressed in degrees. |
Function Name | Brief Description |
RAISE_ERROR | Causes the statement that includes the function to return an error with the specified SQLSTATE, SQLCODE, and diagnostic-string. |
RAND | Returns a random floating-point value between 0 and 1 using the argument as the optional seed value. |
REAL | Returns a single-precision, floating-point representation of a number. |
REC2XML | Returns a string formatted with XML tags and containing column names and column data. |
REPEAT | Returns string1 repeated n times. |
REPLACE | Replaces all occurrences of expression2 in expression1 with expression3. |
RIGHT | Returns a string consisting of the rightmost expression2 bytes in expression1. |
ROUND | Returns number n rounded to m decimal places. |
RTRIM | Returns the characters of the argument with trailing blanks removed. |
SECOND | Returns the seconds part of a time value/expression. |
SIGN | Returns an indicator of the sign of the argument. If the argument is less than zero, -1 is returned. If argument equals zero, 0 is returned. If argument is greater than zero, 1 is returned. |
SIN | Returns the sine of the argument, where the argument is an angle expressed in radians. |
SMALLINT | Returns a small integer representation of a number or character string in the form of a small integer constant. |
SOUNDEX | Returns a four-character code representing the sound of the words in the argument. |
SPACE | Returns a string of n blanks. |
SQRT | Returns the square root of the argument. |
SUBSTR | Returns a part of a string starting from nth characters for the length of m characters. |
TABLE_NAME | Returns the unqualified name of the object found after any alias chains have been resolved. |
TABLE_SCHEMA | Returns the schema name of the object found after any alias chains have been resolved. |
TAN | Returns the tangent of the argument, where the argument is an angle expressed in radians. |
TIME | Returns a time from a value. |
TAMESTAMP | Returns a timestamp from a value or a pair of values. |
TIMESTAMP_ISO | Returns a timestamp value based on date, time, or timestamp argument. |
TIMESTAMPDIFF | Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps. |
TRANSLATE | Replaces all occurrences of string1 within string2 translated into string3. |
TRUN[CATE] | Returns n truncated to m decimal places. |
Function Name | Brief Description |
TYPE_ID | Returns the internal type identifier of the dynamic data type of the expression. |
TYPE_NAME | Returns the unqualified name of the dynamic data type of the expression. |
TYPE_SCHEMA | Returns the schema name of the dynamic data type of the expression. |
UCASE | Returns a string converted into UPPER case. |
UPPER | Returns a string converted into UPPER case. |
VALUE | Returns the first argument that is not NULL. |
VARCHAR | Returns a varying-length character string representation of a character string, datetime value, or graphic string. |
VARGRAPHIC | Returns a graphic string representation of a character string value, converting single-byte characters to double-byte characters; or a graphic string value, if the first argument is any type of graphic string. |
WEEK | Returns the week of the year of the argument as an integer value in the range of 1–54. The week starts with Sunday. |
WEEK_ISO | Returns the week of the year of the argument as an integer value in the range 1–53. |
YEAR | Returns the year part of a value. |
Function Name | Brief Description |
---|---|
AVG | Returns the average of a set of numbers. |
CORR[ELATION] | Returns the coefficient of correlation of a set of number pairs. |
COUNT | Returns the number of rows or values in a set of rows or values. |
COUNT_BIG | Returns the number of rows or values in a set of rows or values. It is similar to COUNT except that the result can be greater than the maximum value of integer. |
COVARIANCE | Returns the (population) covariance of a set of number pairs. |
GROUPING | In conjunction with the GROUP BY clause, returns a value that indicates whether a row returned in a GROUP BY set is a row generated by a grouping set that excludes the column represented by an expression. |
MAX | Returns the maximum value in a set of values. |
MIN | Returns the minimum value in a set of values. |
STDDEV | Returns the standard deviation of a set of numbers. |
SUM | Returns the sum of a set of numbers. |
VAR[IANCE] | Returns the variance of a set of numbers. |
Function Name | Brief Description |
---|---|
MQREADALL | Returns a table containing the messages and message metadata from the MQSeries location specified by receive service, using the quality of service policy. |
MQRECEIVEALL | Returns a table containing the messages and message metadata from the MQSeries location specified by receive service.. The read is non-desctructive — i.e. the message itself remains in the queue. |
SQLCACHE_SNAPSHOT | Returns the results of a snapshot of the DB2 dynamic SQL statement cache. |
Function Name | Brief Description |
---|---|
GET_ROUTINE_SAR | Retrieves the necessary information to install the same routine in another database server, running at the same level on the same operating system. |
PUT_ROUTINE_SAR | Passes the necessary file to create an SQL routine at the server and then defines the routine. |
Function Name | Brief Description |
---|---|
ASCII | Returns the ASCII code of the first character in the expression. |
CHAR | Returns the character for the ASCII code. |
CHARINDEX | Returns the first position of the first occurrence of the expression within another expression. |
DIFFERENCE | Returns the integer difference between two SOUNDEX expressions. |
LEFT | Returns part of the expression starting from a specific character to the left. |
LEN | Returns number of characters in the expression, excluding trailing blank spaces. |
LOWER | Returns an expression with all characters converted to lowercase. |
LTRIM | Returns an expression without left trailing blanks. |
NCHAR | Returns a Unicode character from the code number. |
PATINDEX | Returns starting position of the first occurrence of a pattern within a specified expression. |
REPLACE | Returns string where all occurrences of the second expression within the first expression are replaced with the third expression. |
QUOTENAME | Returns a Unicode expression with delimiters added for validation. |
REPLICATE | Returns an expression consisting of first argument repeated n times. |
REVERSE | Returns a reversed-character expression. |
RIGHT | Returns part of the expression starting from a specific character to the right. |
RTRIM | Returns the expression with trailing blanks removed. |
SOUNDEX | Returns four characters code to evaluate similarity between the sounds of two expressions. |
SPACE | Returns string comprised of the blank spaces, repeated n times. |
STR | Returns character data of numeric data type. |
STUFF | Deletes a specified number of characters, and inserts another set of characters at the specified point. |
SUBSTRING | Returns part of a string, starting from a specified point and spanning a specified number of characters. |
UNICODE | Returns a Unicode integer code for the first character in the expression. |
UPPER | Returns a character string converted to uppercase letters. |
Function Name | Brief Description |
---|---|
ABS | Returns the absolute value of the expression. |
ACOS | Returns the angle in radians for the given cosine. |
ASIN | Returns the angle in radians for the given sine. |
ATAN | Returns the angle in radians for the given tangent. |
ATN2 | Returns the angle in radians whose tangent is in between two given floats. |
CEILING | Returns the smallest integer greater than or equal to a given expression. |
COS | Returns the cosine from a given angle (in radians). |
COT | Returns the cotangent from a given angle (in radians). |
DEGREES | Returns a degrees value from a given expression (in radians). |
EXP | Returns the exponential value of the expression. |
FLOOR | Returns the largest integer that is less than or equal to the given expression. |
LOG | Returns the natural logarithm of a given expression. |
LOG10 | Returns the base 10 logarithm of the given expression. |
PI | Returns number PI (3.1415926535897931...). |
POWER | Returns the result of an expression in power n. |
RADIANS | Returns radians from degrees. |
RAND | Returns the random float number in the range of 0 to 1. |
ROUND | Returns a numeric expression rounded to a specified length or precision. |
SIGN | Returns +1 for positive expressions, 0 for zero, and –1 for negative expressions. |
SIN | Returns the sine from a given angle (in radians). |
SQUARE | Returns the expression squared. |
SQRT | Returns the square root of the given expression. |
TAN | Returns the tangent of the given expression. |
Function Name | Brief Description |
---|---|
DATEADD | Returns a new datetime value based on the passed value plus a specified interval. |
DATEDIFF | Returns number of time units (seconds, days, years, etc.) passed between two dates. |
DATENAME | Returns character string representing a specified date part of the date. |
DATEPART | Returns an integer representing the specified date part. |
DAY | Returns an integer representing the day part of a date. |
GETDATE | Returns the current system's date and time. |
GETUTCDATE | Returns date/time value for the current UTC time. |
MONTH | Returns an integer representing the month part of a date. |
YEAR | Returns an integer representing the year part of a date. |
Function Name | Brief Description |
---|---|
AVG | Returns the average of all the group values; NULLs are ignored. |
BINARY_CHECKSUM | Returns the binary checksum value computed over a row of a table or a list of expressions. |
CHECKSUM | Returns the checksum value computed over a row of a table or a list of expressions. |
CHECKSUM_AGG | Returns the checksum of values in a group. |
COUNT | Returns the number of selected rows or input values. |
COUNT_BIG | Returns the number of selected rows or input values. |
GROUPING | Causes an additional column to be output with a value 1 or 0. |
MIN | Returns the lowest input value. |
MAX | Returns the greatest input value. |
STDEV | Returns the statistical standard deviation for values in the expression. |
STDEVP | Returns the statistical standard deviation for the population of the values in the expression. |
SUM | Returns the sum of the input values. |
VAR | Returns the statistical variance for the values in a given expression. |
VARP | Returns the statistical variance for a population of s in the values in a given expression. |
Function Name | Brief Description |
---|---|
PATINDEX | Returns the starting position of a first occurrence of the specified pattern within an expression. |
TEXTPTR | Returns the text pointer value that corresponds to a text, ntext, or image in varbinary format. |
TEXTVALID | Returns the results from checking whether text, ntext, or image text-pointer is valid. |
Function Name | Brief Description |
---|---|
@@CURSOR_ROWS | Returns the number of rows in the last opened cursor. |
@@FETCH_STATUS | Returns the status of the last FETCH statement from any cursor within the current session. |
CURSOR_STATUS | Is a scalar function that shows whether the procedure has returned a cursor and a result set for a given parameter. |
Function Name | Brief Description |
---|---|
COL_LENGTH | Returns the defined length of a column. |
COL_NAME | Returns the column name from the column ID. |
COLUMNPROPERTY | Returns information about a column or a parameter in a procedure. |
DATABASEPROPERTY | Returns the property value of a given database. |
DATABASEPROPERTYEX | Returns the current setting for the specified property. |
DB_ID | Returns database identification number. |
DB_NAME | Returns the current database name. |
FILE_ID | Returns the file identification number for a given logical file. |
FILE_NAME | Returns the file name from a given identification number. |
FILEGROUP_ID | Returns the file identification number for a given filegroup. |
FILEGROUP_NAME | Returns the filegroup name from a given identification number. |
FILEGROUPPROPERTY | Returns a specified filegroup property value for a given filegroup. |
FILEPROPERTY | Returns a specified file property value for a given file. |
FULLTEXTCATALOGPROPERTY | Returns information about full-text catalog. |
FULLTEXTSERVICEPROPERTY | Returns information about full-text service level properties. |
INDEX_COL | Returns indexed column name. |
INDEXKEY_PROPERTY | Returns information about the index key. |
INDEXPROPERTY | Returns the property value from a given index name and table ID. |
OBJECT_ID | Returns database object's ID given name. |
OBJECT_NAME | Returns database object's name by ID. |
OBJECTPROPERTY | Returns information about objects in the current database. |
@@PROCID | Returns stored procedure identifier for the current procedure. |
TYPEPROPERTY | Returns information about data types. |
SQL_VARIANT_PROPERTY | Returns information about the base data type in addition to other property information. |
FN_LISTEXTENDEDPROPERTY | Returns extended property values of the database objects. |
Function Name | Brief Description |
---|---|
@@CONNECTION | Returns the number of opened or attempted connections. |
@@DATEFIRST | Returns the value of the SET DATEFIRST parameter. |
@@DBTS | Returns the current value of the datestamp data type. |
@@LANGUAGE | Returns the name of the language for the current session/database. |
@@LANGID | Returns ID of the language for the current session/database. |
@@LOCK_TIMEOUT | Returns lock timeout in seconds. |
@@MAX_CONNECTION | Returns the maximum number of simultaneous user connections. |
@@MAX_PRECISION | Returns the precision level used by numeric data types. |
@@NESTLEVEL | Returns the nesting level of the current stored procedure. |
@@OPTIONS | Returns bitmask information about current SET options. |
@@REMSERVER | Returns name of the remote server. |
@@SPID | Returns the number (ID) of the current process/session. |
@@SERVERNAME | Returns the name of the local server. |
@@SERVICENAME | Returns the name of the registry key under which the SQL Server instance is running. |
@@TEXTSIZE | Returns the current value of the TEXTSIZE option. |
@@VERSION | Returns the date, version, and processor type for the current version of SQL Server. |
Function Name | Brief Description |
---|---|
IS_MEMBER | Indicates whether the user is a member of a Windows NT group or an SQL Server role. |
IS_SRVROLEMEMBER | Indicates whether current login is a member of the specified role. |
SUSER_ID | Returns login ID for the current user. |
SUSER_NAME | Returns login name for the current user. |
SUSER_SID | Returns user's security identification number (SID) from login name. |
SUSER_SNAME | Returns user's login name from security identification number (SID). |
USER_ID | Returns user's database identification number from user name. |
USER_NAME | Returns database user's name from identification number. |
USER | Returns the current user's database name. |
HAS_DBACCESS | Indicates whether current user has access to the specified database. |
fn_trace_geteventinfo | Returns information about events being traced. |
fn_trace_getfilterinfo | Returns information about filters applied to a specified trace. |
fn_trace_getinfo | Returns information about traces. |
fn_trace_gettable | Returns trace information in a table format. |
Function Name | Brief Description |
---|---|
APP_NAME | Returns the application name of the current session (if set). |
CASE | Evaluates a list of conditions and returns one value. |
CAST | Explicitly converts one data type into another data type. |
COALESCE | Returns the first non-Null expression on the list. |
CONVERT | Explicitly converts one data type into another data type, behaves similar to the CAST function. |
CURRENT_TIMESTAMP | Returns the current date/time; equivalent of GETDATE(). |
CURRENT_USER | Returns the current user; equivalent of USER_NAME. |
DATALENGTH | Returns the number of bytes in an expression. |
@@ERROR | Returns the error number of the last Transact-SQL statement. |
FORMATMESSAGE | Formats a message from the existing one in the sysmessages table. |
GETANSINULL | Returns the default NULL ability for the database for the session. |
HOST_ID | Returns the ID of the computer. |
HOST_NAME | Returns the name of the host computer. |
IDENT_INCR | Returns the increment value of any identity column in a table or a view. |
IDENT_SEED | Returns the identity seed value of any identity column in a table or a view. |
@@IDENTITY | Returns the last inserted identity value. |
IDENTITY | Used to insert into an identity column. |
ISDATE | Determines whether an expression is a valid date type (or could be converted into one). |
ISNULL | Determines whether the expression is NULL. |
ISNUMERIC | Determines whether the expression is numeric. |
NEWID | Returns a unique value for the UNIQUEIDENTIFIER data type. |
NULLIF | Returns NULL if two expressions are equivalent. |
PARSENAME | Returns the specified path of the object name. |
PERMISSIONS | Returns a value for the bitmap specifying permissions for the object for the current user. |
@@ROWCOUNT | Returns the number of rows affected by the last statement. |
ROWCOUNT_BIG | Returns the rows affected by the last statement (bigint). |
SESSION_USER | Returns the user's name that is to be inserted into a table when no default is specified. |
STATS_DATE | Returns the date when the index statistics were updated. |
SYSTEM_USER | Returns the name of the current user with admin privileges (Windows authenticated). |
@@TRANCOUNT | Returns the number of pending transactions for the current session. |
Function Name | Brief Description |
USER_NAME | Returns the database user name from a given identification number. |
COLLATIONPROPERTY | Returns the property of a given collation. |
SCOPE_IDENTITY | Returns the last identity value inserted in the identity column for the current scope. |
SERVERPROPERTY | Returns the value for the specified server property. |
SESSIONPROPERTY | Returns the value for the specified session property. |
fn_helpcollation | Returns a list of all collation supported. |
fn_servershareddrives | Returns the names of the shared drives that could be used by the clustered server. |
fn_virtualservernodes | Returns a list of nodes on which a virtual server can run. |
Function Name | Brief Description |
---|---|
@@CPU_BUSY | Returns the time (in milliseconds) since the start of the SQL Server. |
@@IDLE | Returns idle time (in milliseconds) since the start of the SQL Server. |
@@IO_BUSY | Returns the time (in milliseconds) since the start of the SQL Server, when it was busy with I/O operations. |
@@PACK_RECEIVED | Returns the number of input packets received since the start of the SQL Server. |
@@PACK_SENT | Returns the number of output packets received since the start of the SQL Server. |
@@PACKET_ERRORS | Returns the number of error packets on the network that have occurred since the start of the SQL Server. |
@@TIMETICKS | Returns the number of milliseconds per CPU tick. |
@@TOTAL_ERRORS | Returns the number of disk write/read errors since the start of the SQL Server. |
@@TOTAL_READ | Returns the number of physical disk reads since the start of the SQL Server. |
@@TOTAL_WRITE | Returns the number of physical disk writes since the start of the SQL Server. |
fn_virtualfilestats | Returns I/O statistics for the database files. |
Function Name | Brief Description |
---|---|
CONTAINSTABLE | Returns a table for those columns containing character-based data types for precise or fuzzy matches of a single word or phrase. |
FREETEXTTABLE | Returns a table for those columns containing character-based data types for values that match the meaning but not the exact word. |
OPENQUERY | Executes a specified pass-through query on a linked OLEDB data source. |
OPENROWSET | Provides an ad-hoc method of connecting and accessing remote data through OLEDB. |
OPENDATASOURCE | Provides ad-hoc connection information, as a part of a four-part object name, without using a linked server. |
OPENXML | Provides a rowset view of the XML document. |
|
|