AVG([DISTINCT] expression)

The AVG function returns the average (arithmetic mean) of the input expression values. The AVG function works with numeric values and ignores NULL values.

• expression: The expression to use to compute the average.

COUNT([DISTINCT] expression)

The COUNT function counts the rows defined by the expression.

• expression: The expression to use to compute the count.

MAX([DISTINCT] expression)

The MIN function returns the minimum value in a set of rows. DISTINCT may be used but do not affect the result.

• expression: The expression to use to compute the max.

MIN([DISTINCT] expression)

The MIN function returns the minimum value in a set of rows. DISTINCT may be used but do not affect the result.

• expression: The expression to use to compute the min.

SUM([DISTINCT] expression)

Returns the sum on non-null values. Each distinct value of expression is aggregated only once into the result.

• expression: The expression to use to compute the sum.

COALESCE(expr1 [, expr2 [, ...]])

An NVL or COALESCE expression returns the value of the first expression in the list that is not null. If all expressions are null, the result is null. When a non-null value is found, the remaining expressions in the list are not evaluated.

• expr1: Any valid expression.
• expr2: Any valid expression.

NVL(expr1 [, expr2 [, ...]])

An NVL or COALESCE expression returns the value of the first expression in the list that is not null. If all expressions are null, the result is null. When a non-null value is found, the remaining expressions in the list are not evaluated.

• expr1: Any valid expression.
• expr2: Any valid expression.

DECODE(expression, search, result [[, search2, result2], default])

A DECODE expression replaces a specific value with either another specific value or a default value, depending on the result of an equality condition. This operation is equivalent to the operation of a simple CASE expression or an IF-THEN-ELSE statement.

• expression: The source of the value that you want to compare, such as a column in a table.
• search: The target value that is compared against the source expression, such as a numeric value or a character string. The search expression must evaluate to a single fixed value.
• result: The replacement value that query returns when the expression matches the search value.
• search2: The target value that is compared against the source expression, such as a numeric value or a character string. The search expression must evaluate to a single fixed value.
• result2: The replacement value that query returns when the expression matches the search value.
• default: An optional default value that is used for cases when the search condition fails. If you do not specify a default value, the DECODE expression returns NULL.

GREATEST(expr1 [, expr2 [, ...]])

Returns the largest value from a list of any number of expressions.

• expr1: Any valid expression.
• expr2: Any valid expression.

LEAST(expr1 [, expr2 [, ...]])

Returns the smallest value from a list of any number of expressions.

• expr1: Any valid expression.
• expr2: Any valid expression.

NULLIF(expression1, expression2)

The NULLIF expression compares two arguments and returns null if the arguments are equal. If they are not equal, the first argument is returned. This expression is the inverse of the NVL or COALESCE expression.

• expression1: The target columns or expressions that are compared.
• expression2: The target columns or expressions that are compared.

ADD_MONTHS(expression, num_months)

ADD_MONTHS adds the specified number of months to a date or timestamp value or expression.

• expression: A date or timestamp column or an expression that implicitly converts to a date or timestamp.
• num_months: A positive or negative integer. Use a negative number to subtract months from dates.

CURRENT_DATE()

CURRENT_DATE returns a date in the current session time zone (UTC by default) in the default format: yyyy-MM-dd.

CURRENT_TIME()

CURRENT_TIME returns a time in the current session time zone (UTC by default) in the default format: hh:mm:ss.mmm.

CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP returns a timestamp in the current session time zone (UTC by default) in the default format: yyyy-MM-dd'T'hh:mm:ss.mmm'Z'.

DATEADD(datepart, interval, expression)

Increments a date or timestamp value by a specified interval.

• datepart: The date part (year, month, or day, for example) that the function operates on.
• interval: An integer that specified the interval (number of days, for example) to add to the target expression. A negative integer subtracts the interval.
• expression: A date or timestamp column or an expression that implicitly converts to a date or timestamp. The date or timestamp expression must contain the specified date part.

DATEDIFF(datepart, expression1, expression2)

DATEDIFF returns the difference between the date parts of two date or time expressions. If the second date or time is later than the first date or time, the result is positive. If the second date or time is earlier than the first date or time, the result is negative.

• datepart: The specific part of the date value (year, month, or day, for example) that the function operates on.
• expression1: A date or timestamp column or expression that implicitly converts to a date or timestamp. The expressions must both contain the specified date part.
• expression2: A date or timestamp column or expression that implicitly converts to a date or timestamp. The expressions must both contain the specified date part.

DATEPART(datepart, expression)

DATEPART extracts datepart values from an expression.

• datepart: The specific part of the date value (year, month, or day, for example) that the function operates on.
• expression: A date or timestamp column or an expression that implicitly converts to a date or timestamp. The expression must be a date or timestamp expression that contains the specified datepart.

TIMESTAMPADD(datetimepart, interval, expression)

Increments a date or timestamp value by a specified interval.

• datetimepart: The datetime part (year, quarter, month, week, day, hour, minute, or seconds for example) that the function operates on.
• interval: An integer that specified the interval (number of days, for example) to add to the target expression. A negative integer subtracts the interval.
• expression: A timestamp column or an expression that implicitly converts to a timestamp. The timestamp expression must contain the specified datetime part.

TIMESTAMPDIFF(datetimepart, expression1, expression2)

TIMESTAMPDIFF returns the difference between the date parts of two date or time expressions. If the second date or time is later than the first date or time, the result is positive. If the second date or time is earlier than the first date or time, the result is negative.

• datepart: The specific part of the date value (year, quarter, month, week, day, hour, minute, or seconds for example) that the function operates on.
• expression1: A timestamp column or expressions that implicitly converts to a timestamp. The expressions must both contain the specified datetime part.
• expression2: A timestamp column or expressions that implicitly converts to a timestamp. The expressions must both contain the specified datetime part.

DAY(date)

DAY returns the day of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

DAYNAME(date)

DAYNAME returns the name of day of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

DAYOFMONTH(date)

DAYOFMONTH returns the day of the month of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

DAYOFWEEK(date)

DAYOFWEEK returns the day of the week of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

DAYOFYEAR(date)

DAYOFYEAR returns the day of the year of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

LAST_DAY(expression)

LAST_DAY returns the date of the last day of the month that contains date.

• expression: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

MONTHS_BETWEEN(date1, date2)

MONTHS_BETWEEN determines the number of months between two dates. If the first date is later than the second date, the result is positive; otherwise, the result is negative. If either argument is null, the result is NULL.

• date1: An expression, such as a column name, that evaluates to a valid date or timestamp value.
• date2: An expression, such as a column name, that evaluates to a valid date or timestamp value.

NEXT_DAY(expression, day)

NEXT_DAY returns the date of the first instance of the specified day that is later than the given date. If the day value is the same day of the week as given_date, the next occurrence of that day is returned.

• expression: A date or timestamp column or an expression that implicitly converts to a date or timestamp.
• day: A string containing the name of any day. Capitalization does not matter.

HOUR(timestamp)

HOUR returns the hour of the specified timestamp.

• timestamp: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

MINUTE(timestamp)

MINUTE returns the minute of the specified timestamp.

• timestamp: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

SECOND(timestamp)

SECOND returns the seconds of the specified timestamp.

• timestamp: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

WEEK(date)

WEEK returns the week of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

MONTH(date)

MONTH returns the month of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

MONTHNAME(date)

MONTHNAME returns the name of month of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

QUARTER(date)

QUARTER returns the quarter of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

YEAR(date)

YEAR returns the year of the specified date.

• date: A date or timestamp column or an expression that implicitly converts to a date or timestamp.

TRUNC(expression [, decimalplaces])

Truncates a timestamp and returns a date.

• expression: Can be a numeric data type or a timestamp column or an expression that implicitly converts to a timestamp.
• decimalplaces: An integer that indicates the number of decimal places of precision, in either direction.

ABS(expression)

ABS calculates the absolute value of a number, where that number can be a literal or an expression that evaluates to a number.

• expression: Number or expression that evaluates to a number.

ACOS(expression)

ACOS is a trigonometric function that returns the arc cosine of a number. The return value is in radians and is between PI/2 and -PI/2.

• expression: A double precision number.

ASIN(expression)

ASIN is a trigonometric function that returns the arc sine of a number. The return value is in radians and is between PI/2 and -PI/2.

• expression: A double precision number.

ATAN(expression)

ATAN is a trigonometric function that returns the arc tangent of a number. The return value is in radians and is between PI/2 and -PI/2.

• expression: A double precision number.

ATAN2(expression1, expression2)

ATAN2 is a trigonometric function that returns the arc tangent of a one number divided by another number. The return value is in radians and is between PI/2 and -PI/2.

• expression1: A double precision number.
• expression2: A double precision number.

CEIL(expression)

The CEILING or CEIL function is used to round a number up to the next whole number.

• expression: A double precision number.

CEILING(expression)

The CEILING or CEIL function is used to round a number up to the next whole number.

• expression: A double precision number.

COS(expression)

COS is a trigonometric function that returns the cosine of a number. The return value is in radians and is between PI/2 and -PI/2.

• expression: A double precision number.

COT(expression)

COT is a trigonometric function that returns the cotangent of a number. The input parameter must be nonzero.

• expression: A double precision number.

DEGREES(expression)

Converts an angle in radians to its equivalent in degrees.

• expression: A double precision number.

EXP(expression)

The EXP function returns the exponential value in scientific notation for a numeric expression.

• expression: The expression must be an INTEGER, DECIMAL, or DOUBLE PRECISION data type.

FLOOR(expression)

The FLOOR function rounds a number down to the next whole number.

• expression: A double precision number.

LN(expression)

Returns the natural logarithm of the input parameter.

• expression: The target column or expression that the function operates on.

MOD(number1, number2)

The MOD function returns a numeric result that is the remainder of two numeric parameters. The first parameter is divided by the second parameter.

• number1: The first input parameter is an INTEGER, SMALLINT, BIGINT, or DECIMAL number.
• number2: The second parameter is an INTEGER, SMALLINT, BIGINT, or DECIMAL number. The same data type rules apply to number2 as to number1.

PI()

The PI function returns the value of PI to 14 decimal places.

POW(expression1, expression2)

The POW function is an exponential function that raises a numeric expression to the power of a second numeric expression.

• expression1: Numeric expression to be raised. Must be an integer, decimal, or floating-point data type.
• expression2: Power to raise expression1. Must be an integer, decimal, or floating-point data type.

POWER(expression1, expression2)

The POWER function is an exponential function that raises a numeric expression to the power of a second numeric expression.

• expression1: Numeric expression to be raised. Must be an integer, decimal, or floating-point data type.
• expression2: Power to raise expression1. Must be an integer, decimal, or floating-point data type.

RADIANS(expression)

Converts an angle in degrees to its equivalent in radians.

• expression: A double precision number.

RANDOM()

The RANDOM function generates a random value between 0.0 and 1.0.

ROUND(expression [, decimalplaces])

The ROUND function rounds numbers to the nearest integer or decimal.

• expression: INTEGER, DECIMAL, and FLOAT data types are supported.
• decimalplaces: An integer to indicate the number of decimal places for rounding.

SIN(expression)

SIN is a trigonometric function that returns the sine of a number. The return value is between -1 and 1.

• expression: A double precision number.

SIGN(expression)

The SIGN function returns the sign (positive or negative) of a number. The result of the SIGN function is 1, -1, or 0 indicating the sign of the argument.

• expression: Number to be evaluated. The data type can be numeric or double precision.

SQRT(expression)

The SQRT function returns the square root of a numeric value.

• expression: The expression must have an integer, decimal, or floating-point data type.

TAN(expression)

TAN is a trigonometric function that returns the tangent of a number. The input parameter must be a non-zero number (in radians).

• expression: A double precision number.

ASCII(expression)

ASCII returns the sequence of Unicode code points that constitute $expression.

• expression: A string expression.

LEN(expression)

Returns the length of the specified string as the number of characters.

• expression: A string expression.

CHAR_LENGTH(expression)

Returns the length of the specified string as the number of characters.

• expression: A string expression.

CHARACTER_LENGTH(expression)

Returns the length of the specified string as the number of characters.

• expression: A string expression.

LENGTH(expression)

Returns the length of the specified string as the number of characters.

• expression: A string expression.

TEXTLEN(expression)

Returns the length of the specified string as the number of characters.

• expression: A string expression.

BIT_LENGTH(expression)

Returns the length of the specified string in bits.

• expression: A string expression.

CONCAT(str1, str2)

The CONCAT function concatenates two character string and returns the resulting string.

• str1: A string expression.
• str2: A string expression.

INITCAP(expression)

Capitalizes the first letter of each word in a specified string. INITCAP supports UTF-8 multibyte characters, up to a maximum of four bytes per character.

• expression: A string expression.

LEFT(expression, num)

Returns the specified number of leftmost characters from a character string.

• expression: A string expression.
• num: A positive integer.

RIGHT(expression, num)

Returns the specified number of rightmost characters from a character string.

• expression: A string expression.
• num: A positive integer.

LOWER(expression)

Converts a string to lowercase. LOWER supports UTF-8 multibyte characters, up to a maximum of four bytes per character.

• expression: A string expression.

RPAD(string1, length [, string2])

Appends characters to a string, based on a specified length.

• string1: A character string or an expression that evaluates to a character string, such as the name of a character column.
• length: An integer that defines the length of the result of the function.
• string2: One or more characters that are appended to string1. This argument is optional; if it is not specified, spaces are used.

LPAD(string1, length [, string2])

Prepends characters to a string, based on a specified length.

• string1: A character string or an expression that evaluates to a character string, such as the name of a character column.
• length: An integer that defines the length of the result of the function.
• string2: One or more characters that are prepended to string1. This argument is optional; if it is not specified, spaces are used.

LTRIM(expression)

The LTRIM function trims a leading spaces of a string.

• expression: The string column or expression to be trimmed.

TRIMLEADING(expression)

The TRIMLEADING function trims a leading spaces of a string.

• expression: The string column or expression to be trimmed.

OCTET_LENGTH(expression)

Returns the length of the specified string as the number of bytes.

• expression: A string expression.

POSITION(substring IN string)

Returns the location of the specified substring within a string.

• substring: The substring to search for within the string.
• string: The string or column to be searched.

REPEAT(expression, numrepeats)

Repeats a string the specified number of times. If the input parameter is numeric, REPEAT treats it as a string.

• expression: The first input parameter is the string to be repeated.
• numrepeats: An integer indicating the number of times to repeat the string.

REPLACE(expression, old_chars, new_chars)

Replaces all occurrences of a set of characters within an existing string with other specified characters.

• expression: CHAR or VARCHAR string to be searched search
• old_chars: CHAR or VARCHAR string to replace.
• new_chars: New CHAR or VARCHAR string replacing the old_string.

RTRIM(expression)

The RTRIM function trims trailing spaces of a string.

• expression: The string column or expression to be trimmed.

STRPOS(string, substring)

Returns the position of a substring within a specified string.

• string: The string to be searched.
• substring: The substring to search for within the string.

SUBSTRING(expression, start_position, number_chars)

Returns the characters extracted from a string based on the specified character position for a specified number of characters.

• expression: The string to be searched. Non-character data types are treated like a string.
• start_position: The position within the string to begin the extraction, starting at 1.
• number_chars: The number of characters to extract (the length of the substring).

TRANSLATE(expression, characters_to_replace, characters_to_substitute)

For a given expression, replaces all occurrences of specified characters with specified substitutes. Existing characters are mapped to replacement characters by their positions in the characters_to_replace and characters_to_substitute arguments.

• expression: The expression to be translated.
• characters_to_replace: A string containing the characters to be replaced.
• characters_to_substitute: A string containing the characters to substitute.

UPPER(expression)

Converts a string to uppercase. UPPER supports UTF-8 multibyte characters, up to a maximum of four bytes per character.

• expression: A string expression.

CAST(expression AS type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.

• expression: The expression to cast.
• type: The type to cast the expression to.

USER()

Returns the user name of the current "effective" user of the database, as applicable to checking permissions. Usually, this user name will be the same as the session user; however, this can occasionally be changed by superusers.