Appendix G: SQL Functions

Overview

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.

Table G-1: Oracle 9i SQL Number Functions
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.

Table G-2: Oracle 9i SQL Character Functions
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.

Table G-3: Oracle 9i SQL Datetime Functions
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.

Table G-4: Oracle 9i SQL Conversion Functions
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.

Table G-5: Oracle 9i SQL Miscellaneous Single-Row Functions
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.

Table G-6: Oracle 9i SQL Aggregate Functions
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.

Table G-7: Oracle 9i SQL Analytic Functions
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