All Products
Search
Document Center

Function overview

Last Updated: Jun 18, 2021

Functions work in a similar way as operators. After data elements are passed to a function as the input of parameters, the function returns a result. However, functions differ from operators in the format of input parameters. Functions can contain a variable number of parameters. A function can contain one, two, or more parameters.

Function name (parameter, parameter, ...)

A function without parameters is similar to a pseudocolumn. For more information, see Pseudo columns. A pseudocolumn returns a different value for each row in the result set. However, a function without variables returns the same value for all rows.

About functions

Built-in functions of ApsaraDB for OceanBase can be used in SQL statements. An expected data type is specified for each parameter of a function. If the data type of an input argument is expected, ApsaraDB for OceanBase attempts to convert the data type to the expected one and then executes the SQL statement.

Null values in functions

If the input argument is NULL, most functions return NULL as the result. In this case, you can use the NVL function to return a non-null value. For example, assume that a table is created to store commission data and the commission_pct column stores the commission values. If the value in the commission_pct column is NULL, the NVL(commission_pct,0) expression returns 0. If the value in the commission_pct column is not NULL, the expression returns the actual commission value.

Function types and lists

In the following function lists, each parameter or return value of each function has a specific data type.

Notice

When you apply a function to a LOB column in an SQL statement, ApsaraDB for OceanBase creates a temporary LOB column during SQL processing or PL/SQL processing. Some limits are set for using the column. For more information, see Compatibility with Oracle databases.

This topic describes two categories of functions:

  • Single-row functions: include numeric functions, string functions that return strings, string functions that return numbers, date and time functions, general comparison functions, conversion functions, encoding and decoding functions, and null-related functions.
  • Statistical functions: include aggregate functions and analytic functions.

A single-row function returns a result value for each row of a queried table or view. These functions can be used in SQL clauses such as SELECT, WHERE, START WITH, CONNECT BY, and HAVING.

Aggregate functions and analytic functions perform aggregate calculations on a group of rows. An aggregate function returns only a single row for each group of rows. However, an analytic function returns multiple rows for each group of rows. A group of rows is also called a window. In most cases, aggregate functions are used in combination with the GROUP BY clause in the SELECT statement. When you use an aggregate function, the database divides the rows of a queried table or view into groups and applies the aggregate function to each group of rows. Then, one result row is returned for each group of rows.

When you use analytic functions, you must use the special keyword OVER to specify a window. For more information about window functions, see Window functions.

Numeric functions

Numeric functions accept numeric values as the input and return numeric values. Most numeric functions return values of

the NUMBER data type. The return values can be accurate to 38 decimal places. Some functions are related to advanced algebra. Some of these functions, such as COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH, return values that are accurate to 36 decimal points. Other functions that are related to advanced algebra, such as ACOS, ASIN, ATAN, and ATAN2. return values that are accurate to 30 decimal places.

Function category

Function subcategory

Function name

Description

Single-row function

Numeric function

ABS

A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Single-row function

Numeric function

ACOS

Returns the angle that is represented in radians, whose cosine is specified by using the

NUMBER

expression. This angle is also called arccosine.

Single-row function

Numeric function

ASIN

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

ATAN

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

ATAN2

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

BITAND

Performs a bitwise operation by using the bitwise AND operator. Both the input and output values are of the same INT type.

Single-row function

Numeric function

CEIL

Returns the smallest integer that is greater than or equal to the value of numeric_expression.

Single-row function

Numeric function

COS

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

COSH

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

EXP

Returns e raised to the power of numeric_expression.

Single-row function

Numeric function

FLOOR

Returns the largest integer that is equal to or less than the value of numeric_expression.

Single-row function

Numeric function

LN

Returns the e-based logarithm of numeric_expression.

Single-row function

Numeric function

LOG

Returns the x-based logarithm of y.

Single-row function

Numeric function

MOD

Returns the remainder that is produced when x is divided by y.

Single-row function

Numeric function

POWER

Returns x raised to the power of y.

Single-row function

Numeric function

REMAINDER

Returns the remainder that is produced when x is divided by y.

Single-row function

Numeric function

ROUND

Returns the rounded value of numeric.

Single-row function

Numeric function

SIGN

Returns the sign of the number n. This function returns 1 if n is greater than 0, returns -1 if n is less than 0, and returns 0 if n is equal to 0.

Single-row function

Numeric function

SIN

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

SINH

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

SQRT

Returns the square root of n.

Single-row function

Numeric function

TAN

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

TANH

ApsaraDB for OceanBase does not support this function.

Single-row function

Numeric function

TRUNC

Truncates numeric to the specified precision and returns the result value.

Single-row function

Numeric function

WIDTH_BUCKET

ApsaraDB for OceanBase does not support this function.

String functions that return strings

The maximum length of the value returned by a function varies based on the data type of the value. For example, assume that a function returns a VARCHAR2 value whose length exceeds the maximum length of a VARCHAR2 value. In this case, ApsaraDB for OceanBase truncates the value and returns the truncated value without displaying a prompt on the client.

Notice

If a function returns a CLOB value whose length exceeds the maximum length of a CLOB value, ApsaraDB for OceanBase returns no data and displays an error message.

Function category

Function subcategory

Function name

Description

Single-row function

String functions that return strings

CHR

Converts n to one or more equivalent characters. The return value is related to the current system character set.

Single-row function

String functions that return strings

CONCAT

Concatenates two strings.

Single-row function

String functions that return strings

INITCAP

Returns a string in which the first letter of each word is uppercase and other letters are lowercase.

Single-row function

String functions that return strings

LOWER

Converts all letters in a string to lowercase letters.

Single-row function

String functions that return strings

LPAD

Left-pads the c1 string to the given length n with the c2 string.

Single-row function

String functions that return strings

LTRIM

Trims the string that appears on the left.

Single-row function

String functions that return strings

REGEXP_REPLACE

Replaces an object with a regular expression.

Single-row function

String functions that return strings

REGEXP_SUBSTR

ApsaraDB for OceanBase does not support this function.

Single-row function

String functions that return strings

REPLACE

Replaces a specified string with a new string in a character expression.

Single-row function

String functions that return strings

RPAD

Right-pads the c1 string to the given length n with the c2 string.

Single-row function

String functions that return strings

RTRIM

Trims a string that appears on the right. This function is useful for formatting the output of a query.

Single-row function

String functions that return strings

SUBSTR

Extracts a substring. A multibyte character, such as a full-width character, is counted as one character.

Single-row function

String functions that return strings

TRANSLATE

Replaces a specified character with a new character in a character expression. A multibyte character, such as a full-width character, is counted as one character.

Single-row function

String functions that return strings

TRIM

Trims leading characters, trailing characters, or both of them from a string.

Single-row function

String functions that return strings

UPPER

Converts all letters in a string to uppercase letters.

String functions that return numbers

Function categoryFunction subcategoryFunction nameDescription

Single-row function

String functions that return numbers

ASCII

Returns the ASCII value of the leftmost character in a character expression.

Single-row function

String functions that return numbers

INSTR

Searches for a specified character in a string, and returns the position where the specified character is found.

Single-row function

String functions that return numbers

LENGTH

Returns the length of a string.

Single-row function

String functions that return numbers

REGEXP_COUNT

ApsaraDB for OceanBase does not support this function.

Single-row function

String functions that return numbers

REGEXP_INSTR

ApsaraDB for OceanBase does not support this function.

Date and time functions

Date and time functions support input parameters of the following data types: date-related data types (DATE), timestamp-related data types (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval-related data types (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).

The following functions support input parameters of only the DATE data type: ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY.

Assume that you attempt to insert a TIMESTAMP value as an argument into the preceding functions. ApsaraDB for OceanBase implicitly converts the data type, passes the converted data to the function for calculations, and then returns a DATE value.

Notice

MONTHS_BETWEEN returns a number.

ROUND and TRUNC cannot implicitly convert data types and support input parameter of only the DATE type. If you pass values of other data types to these functions, errors are reported.

The other date and time functions support input parameters of the three data types and return values of the same data types as the input parameters.

Function category

Function subcategory

Function name

Description

Single-row function

Date and time function

ADD_MONTHS

Returns the date value that is n months after date. If n is a negative number, this function returns the date value that is n months before date.

Single-row function

Date and time function

CURRENT_DATE

Returns the current date in the session time zone.

Single-row function

Date and time function

CURRENT_TIMESTAMP

Returns a value of the TIMESTAMP WITH TIME ZONE data type. The return value indicates the current date in the session time zone and contains the information of the current time zone.

Single-row function

Date and time function

DBTIMEZONE

Returns the time zone of the current database instance. In ApsaraDB for OceanBase, the database time zone is UTC+0 and cannot be changed.

Single-row function

Date and time function

EXTRACT (datetime)

Extracts elements such as the year, month, day, hour, minute, and second values from a specified time field or expression.

Single-row function

Date and time function

FROM_TZ

Combines a value of the TIMESTAMP data type with the time zone information into a time value of the TIMESTAMP WITH TIME ZONE data type.

Single-row function

Date and time function

LAST_DAY

Returns the date of the last day of the month in which the specified date falls.

Single-row function

Date and time function

LOCALTIMESTAMP

Returns a value of the TIMESTAMP data type. The return value indicates the current date in the session time zone.

Single-row function

Date and time function

MONTHS_BETWEEN

Returns the number of months between date1 and date2.

Single-row function

Date and time function

NEW_TIME

ApsaraDB for OceanBase does not support this function.

Single-row function

Date and time function

NEXT_DAY

Returns the date value of the weekday c1 in the week following d1.

Single-row function

Date and time function

NUMTODSINTERVAL

Converts the argument n to a value of the INTERVAL DAY TO SECOND data type. The interval_unit parameter specifies the measurement unit.

Single-row function

Date and time function

NUMTOYMINTERVAL

Converts the argument n to a value of the INTERVAL YEAR TO MONTH data type. The interval_unit parameter specifies the measurement unit.

Single-row function

Date and time function

ROUND (date)

Returns a date and time value that is nearest to the specified date. The fmt parameter specifies the unit that is used to measure the interval between the returned date and the specified date.

Single-row function

Date and time function

SESSIONTIMEZONE

Returns the time zone of the current session.

Single-row function

Date and time function

SYS_EXTRACT_UTC

Returns the UTC time that corresponds to the specified time.

Single-row function

Date and time function

SYSDATE

Returns the current date.

Single-row function

Date and time function

SYSTIMESTAMP

Returns the current system date which contains the current time zone information. Six digits appear after the decimal point of the second value.

Single-row function

Date and time function

TO_CHAR (datetime)

Converts a value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of the VARCHAR2 data type. The fmt parameter specifies the format of the return value.

Single-row function

Date and time function

TO_DSINTERVAL

Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL DAY TO SECOND data type. You can perform the addition and subtraction arithmetic operations on date and time values by using this function.

Single-row function

Date and time function

TO_TIMESTAMP

Converts a string to a value of the TIMESTAMP data type.

Single-row function

Date and time function

TO_TIMESTAMP_TZ

Converts a string to a value of the TIMESTAMP WITH TIME ZONE data type, which contains the time zone information.

Single-row function

Date and time function

TO_YMINTERVAL

Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type. You can perform the addition and subtraction arithmetic operations on date and time values by using this function.

Single-row function

Date and time function

TRUNC (date)

Returns a date and time value that is nearest to the specified date. The fmt parameter specifies the unit in which the interval between the return value and the specified date is measured. The returned date value precedes date.

Single-row function

Date and time function

TZ_OFFSET

Returns the offset of the n time zone. The time zone offset is the difference between a time zone and the UTC+0 time zone in hours and minutes.

General comparison functions

You can use this type of functions to locate the maximum and minimum values in a set of values in a short time.

Function category

Function subcategory

Function name

Description

Single-row function

General comparison function

GREATEST

Returns the maximum value in a list of one or more expressions.

Single-row function

General comparison function

LEAST

Returns the minimum value in a list of one or more expressions.

Conversion functions

You can convert a value from one data type to another data type by using this type of functions.

Function category

Function subcategory

Function name

Description

Function categoryFunction subcategoryFunction nameDescription

Single-row function

Conversion function

ASCIISTR

ApsaraDB for OceanBase does not support this function.

Single-row function

Conversion function

BIN_TO_NUM

ApsaraDB for OceanBase does not support this function.

Single-row function

Conversion function

CHARTOROWID

ApsaraDB for OceanBase does not support this function.

Single-row function

Conversion function

HEXTORAW

Converts a character that contains hexadecimal numbers and is of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the RAW data type.

Single-row function

Conversion function

RAWTOHEX

Converts a binary number to a hexadecimal format string.

Single-row function

Conversion function

TO_BINARY_DOUBLE

Returns a 64-bit double-precision floating-point number.

Single-row function

Conversion function

TO_BINARY_FLOAT

Returns a 32-bit single-precision floating-point number.

Single-row function

Conversion function

TO_CHAR (character)

Converts an NCHAR, NVARCHAR2, or CLOB character to a database character set.

Single-row function

Conversion function

TO_CHAR (datetime)

Converts a value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of the VARCHAR2 data type. The fmt parameter specifies the format of the return value.

Single-row function

Conversion function

TO_CHAR (number)

Converts the value n of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to a value of the VARCHAR2 data type in the format specified by fmt.

Single-row function

Conversion function

TO_DATE

Converts a character of the CHAR, VARCHAR, NCHAR, or NVARCHAR2 data type to a value of the DATE data type.

Single-row function

Conversion function

TO_DSINTERVAL

Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL DAY TO SECOND data type. You can perform the addition and subtraction arithmetic operations on a date and time value by using this function.

Single-row function

Conversion function

TO_NUMBER

Converts expr to a numeric value.

Single-row function

Conversion function

TO_TIMESTAMP

Converts a string to a value of the TIMESTAMP data type.

Single-row function

Conversion function

TO_TIMESTAMP_TZ

Converts a string to a value of the TIMESTAMP WITH TIME ZONE data type, which contains the time zone information.

Single-row function

Conversion function

TO_YMINTERVAL

Converts a string of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type. You can perform the addition and subtraction arithmetic operations on a date and time value by using this function.

Encoding and decoding functions

You can encode and decode data in ApsaraDB for OceanBase by using this type of functions.

Function category

Function subcategory

Function name

Description

Single-row function

Encoding and decoding function

DECODE

Returns a value that matches the specified conditions.

Single-row function

Encoding and decoding function

ORA_HASH

Retrieves the hash value of a corresponding expression.

Single-row function

Encoding and decoding function

VSIZE

Returns the size of X in bytes.

Null value-related functions

Function category

Function subcategory

Function name

Description

Single-row function

Null value-related function

COALESCE

Returns the first non-null expression in a parameter list. You must specify at least two parameters.

Single-row function

Null value-related function

LNNVL

Determines whether one or two operands in a condition are NULL.

Single-row function

Null value-related function

NULLIF

ApsaraDB for OceanBase does not support this function.

Single-row function

Null value-related function

NVL

Returns a non-NULL value from two expressions. If expr1 and expr2 return NULL, the NVL function returns NULL.

Single-row function

Null value-related function

NVL2

Returns different values based on whether an expression is null. If expr1 is not null, this function returns the value of expr2. If expr1 is null, this function returns the value of expr3. If expr2 and expr3 are of different data types, this function converts the data type of expr3 to that of expr1.

Environment-related functions

Functions of this type provide the environment information about sessions or tenants.

Function category

Function subcategory

Function name

Description

Single-row function

Environment-related function

SYS_CONTEXT

ApsaraDB for OceanBase does not support this function.

Single-row function

Environment-related function

UID

ApsaraDB for OceanBase does not support this function.

Single-row function

Environment-related function

USER

ApsaraDB for OceanBase does not support this function.

Aggregate functions

Function category

Function subcategory

Function name

Description

Statistical function

Aggregate function

AVG

Returns the average value of all values in a numeric column.

Statistical function

Aggregate function

COUNT

Queries the number of rows for the expr parameter.

Statistical function

Aggregate function

SUM

Returns the sum of all values in a specified column.

Statistical function

Aggregate function

GROUPING

ApsaraDB for OceanBase does not support this function.

Statistical function

Aggregate function

MAX

Returns the maximum value of a specified column.

Statistical function

Aggregate function

MIN

Returns the minimum value of a specified column.

Statistical function

Aggregate function

LISTAGG

Converts a column to a row. The LISTAGG function sorts the data within each group that is specified in the ORDER BY clause and merges the values in the measure column.

Statistical function

Aggregate function

ROLLUP

Returns a subtotal for each group and a grand total for all groups during data statistical analysis and report generation.

Statistical function

Aggregate function

STDDEV

Calculates the population standard deviation.

Statistical function

Aggregate function

STDDEV_POP

Calculates the population standard deviation.

Statistical function

Aggregate function

STDDEV_SAMP

Calculates the sample standard deviation.

Statistical function

Aggregate function

VARIANCE

Returns the variance of a specified column.

Statistical function

Aggregate function

APPROX_COUNT_DISTINCT

Calculates the number of rows in a column where duplicates are removed, and can return only an approximate value. You can use this function to further calculate the selectivity of the referenced column.

Analytic functions

Function category

Function subcategory

Function name

Description

Statistical function

Analytic function

AVG

Returns the average value of all values in a numeric column.

Statistical function

Analytic function

COUNT

Queries the number of rows for the expr parameter.

Statistical function

Analytic function

CUME_DIST

Calculates the cumulative distribution of a value in a group of values.

Statistical function

Analytic function

DENSE_RANK

Calculates the rank of a row in an ordered group of rows and returns the rank as NUMBER.

Statistical function

Analytic function

MAX

Returns the maximum value of a specified column.

Statistical function

Analytic function

MIN

Returns the minimum value of a specified column.

Statistical function

Analytic function

SUM

Returns the sum of all values in a specified column.

Statistical function

Analytic function

FIRST_VALUE

Returns the first value in a set of ordered values.

Statistical function

Analytic function

LAG

Provides access to a multi-row table without a self join.

Statistical function

Analytic function

LAST_VALUE

Returns the last value in a set of ordered values.

Statistical function

Analytic function

LEAD

Provides access to multiple rows of a table without a self join. Given a set of rows returned from a query and the position of the cursor, LEAD provides access to a row at a given physical offset beyond that position.

Statistical function

Analytic function

LISTAGG

Converts a column to a row.

Statistical function

Analytic function

NTH_VALUE

Returns the value of measure_expr in the nth row of the window defined by analytic_clause.

Statistical function

Analytic function

NTILE

Divides an ordered dataset into several buckets and assigns an appropriate bucket number to each row. expr specifies the number of buckets.

Statistical function

Analytic function

PERCENT_RANK

This function is similar to the CUME_DIST function that calculates the cumulative distribution. The return value ranges from 0 to 1. The PERCENT_RANK function of the first row in a set is 0. The return value is NUMBER.

Statistical function

Analytic function

SUM

Returns the sum of all values in a specified column.

Statistical function

Analytic function

RANK

Determines the rank of a group of values based on the ORDER BY expression in the OVER clause.

Statistical function

Analytic function

RATIO_TO_REPORT

Calculates the ratio of a value to the sum of a group of values.

Statistical function

Analytic function

ROW_NUMBER

Assigns a unique number to each row to which the function is applied.

Statistical function

Analytic function

STDDEV

Calculates the population standard deviation.

Statistical function

Analytic function

STDDEV_POP

Calculates the population standard deviation.

Statistical function

Analytic function

STDDEV_SAMP

Calculates the sample standard deviation.

Statistical function

Analytic function

VARIANCE

Returns the variance of a specified column.

More information

For more information about the OVER keyword of analytic functions, see Window function description.