Appendix G: SQL Functions

Overview

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 9 i 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 9 i 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 9 i 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 9 i 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 9 i 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	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 REGR_INTERCEPT REGR_COUNT REGR_R2 REGR_AVGX REGR_AVGY REGR_SXX REGR_SYY REGR_SXY	The linear regression functions that follow fit an ordinary-least-squares regression line to a set of number pairs: REGR_SLOPE returns the slope of the line. The return value is a number and can be NULL. REGR_INTERCEPT returns the y-intercept of the regression line. REGR_COUNT returns an integer that is the number of non-NULL number pairs used to fit the regression line. REGR_R2 returns the coefficient of determination (also called "R-squared" or "goodness of fit") for the regression. REGR_AVGX evaluates the average of the independent variable (expr2) of the regression line. REGR_AVGY evaluates the average of the dependent variable (expr1) of the regression line. REGR_SXY, REGR_SXX, and REGR_SYY are auxiliary functions that are used to compute various diagnostic statistics.
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.

Table G-8: Oracle 9i SQL Object Reference Functions
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.

Table G-9: IBM DB2 UDB Built-in Scalar Functions
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.

Table G-10: IBM DB2 UDB Built-in Column Functions
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.

Table G-11: IBM DB2 UDB Built-in Table Functions
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.

Table G-12: IBM DB2 UDB Built-in Procedures
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.

Table G-13: Microsoft SQL Server 2000 Built-in String Functions
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.

Table G-14: Microsoft SQL Server 2000 Built-in Mathematical Functions
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.

Table G-15: Microsoft SQL Server 2000 Built-in Date and Time Functions
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.

Table G-16: Microsoft SQL Server 2000 Built-in Aggregate Functions
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.

Table G-17: Microsoft SQL Server 2000 Built-in Text and Image Functions
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.

Table G-18: Microsoft SQL Server 2000 Built-in Cursor Functions
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.

Table G-19: Microsoft SQL Server 2000 Built-in Metadata Functions
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.

Table G-20: Microsoft SQL Server 2000 Built-in Configuration Functions
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.

Table G-21: Microsoft SQL Server 2000 Built-in Security Functions
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.

Table G-22: Microsoft SQL Server 2000 Built-in System Functions
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.

Table G-23: Microsoft SQL Server 2000 Built-in System Statistical Functions
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.

Table G-24: Microsoft SQL Server 2000 Built-in Rowset Functions
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.

Appendix G: SQL Functions - SQL Bible [Electronic resources] نسخه متنی

Appendix G: SQL Functions

Overview

Table G-9: IBM DB2 UDB Built-in Scalar Functions

Table G-10: IBM DB2 UDB Built-in Column Functions

Table G-11: IBM DB2 UDB Built-in Table Functions

Table G-12: IBM DB2 UDB Built-in Procedures

Table G-13: Microsoft SQL Server 2000 Built-in String Functions

Table G-14: Microsoft SQL Server 2000 Built-in Mathematical Functions

Table G-15: Microsoft SQL Server 2000 Built-in Date and Time Functions

Table G-16: Microsoft SQL Server 2000 Built-in Aggregate Functions

Table G-17: Microsoft SQL Server 2000 Built-in Text and Image Functions

Table G-18: Microsoft SQL Server 2000 Built-in Cursor Functions

Table G-19: Microsoft SQL Server 2000 Built-in Metadata Functions

Table G-20: Microsoft SQL Server 2000 Built-in Configuration Functions

Table G-21: Microsoft SQL Server 2000 Built-in Security Functions

Table G-22: Microsoft SQL Server 2000 Built-in System Functions

Table G-23: Microsoft SQL Server 2000 Built-in System Statistical Functions

Table G-24: Microsoft SQL Server 2000 Built-in Rowset Functions