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.

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 riost 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 riost 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

@@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.

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.

@@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.

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.

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.

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.

@@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.

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.