Built-in functions of OceanBase Database can be directly used in SQL statements. Each function expects specific data types for its input parameters. If the provided data type does not match the expected type, OceanBase Database attempts to convert the input value to the expected data type before executing the SQL function.
Functions are similar to operators in that they take input data elements and return results. However, functions differ from operators in terms of parameter handling. Functions can accept a variable number of parameters, ranging from one to multiple.
FunctionName(Parameter, Parameter, ...)
Functions can also have no parameters, similar to pseudocolumns. However, pseudocolumns typically return different values for each row in the result set, while functions without parameters usually return the same value for each row.
Notice
When using functions on LOB columns in SQL statements, OceanBase Database creates temporary LOB columns during SQL and PL processing, with certain usage limitations. For more details, see Oracle compatibility comparison.
This chapter categorizes functions into two main types, each with specific data types for parameters and return values:
Single-row functions: These 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, null value-related functions, environment and identity functions, and hierarchical functions.
Statistical functions: These include aggregate functions and analytical functions.
Single-row functions return a result value for each row in the queried table or view. These functions can be used in SQL statements with clauses such as SELECT, WHERE, START WITH, CONNECT BY, and HAVING.
Analytical functions and aggregate functions both perform aggregate calculations on a group of rows (a set of rows). The difference is that aggregate functions return one value (one row) per group, while analytical functions can return multiple values (multiple rows) per group. A group of rows is also referred to as a window. Aggregate functions are typically used with the GROUP BY clause in SELECT statements. When used, the database divides the rows of the queried table or view into groups and applies the aggregate function to each group, returning one result row per group.
When using analytical functions, you must specify the window using the special keyword OVER. For more information about window functions, see Analytical functions.
Numeric functions
The input and output of numeric functions are numeric types. Most numeric functions return a value of the NUMBER type, which can be precise to 38 decimal places. Some advanced algebraic functions, such as COS(), COSH(), EXP(), LN(), LOG(), SIN(), SINH(), SQRT(), TAN(), and TANH(), return values precise to 36 decimal places. Other algebraic functions, such as ACOS(), ASIN(), ATAN(), and ATAN2(), return values precise to 30 decimal places.
| Function | Description |
|---|---|
| ABS | Returns the absolute value of a specified numeric expression. |
| ACOS | Returns the angle in radians whose cosine is the specified numeric expression. |
| ASIN | Returns the angle in radians whose sine is the specified numeric expression. |
| ATAN | Returns the angle in radians whose tangent is the specified numeric expression. |
| ATAN2 | Returns the angle in radians whose tangent is the ratio of the two specified numeric expressions. |
| BITAND | Performs a bitwise AND operation on two numeric values. |
| CEIL | Returns the smallest integer greater than or equal to the specified numeric value. |
| COS | Returns the cosine of the specified angle. |
| COSH | Returns the hyperbolic cosine of the specified angle. |
| EXP | Returns e raised to the specified power. |
| FLOOR | Returns the largest integer less than or equal to the specified numeric value. |
| GENERATOR | Generates a random number within the specified range. |
| LN | Returns the natural logarithm of the specified numeric value. |
| LOG | Returns the logarithm of the specified numeric value. |
| MOD | Returns the remainder after dividing two numeric values. |
| NANVL | Returns the second argument if the first argument is NaN. |
| NORMAL | Generates a random number from a normal distribution. |
| POWER | Returns the specified numeric value raised to the specified power. |
| RANDOM | Generates a random number between 0 and 1. |
| RANDSTR | Generates a random string of the specified length. |
| REMAINDER | Returns the remainder after dividing two numeric values. |
| ROUND | Rounds the numeric value to the specified number of decimal places. |
| SIGN | Returns the sign of the numeric value (-1, 0, or 1). |
| SIN | Returns the sine of the specified angle. |
| SINH | Returns the hyperbolic sine of the specified angle. |
| SQRT | Returns the square root of the specified numeric value. |
| TAN | Returns the tangent of the specified angle. |
| TANH | Returns the hyperbolic tangent of the specified angle. |
| TRUNC | Truncates the numeric value to the specified number of decimal places. |
| UNIFORM | Generates a uniformly distributed random number within the specified range. |
| WIDTH_BUCKET | Distributes the numeric value into the specified number of buckets. |
| ZIPF | Generates a random number from a Zipf distribution. |
String functions that return strings
The maximum length of the return value of a string function depends on the data type. For example, if the return value of a string function is of the VARCHAR2 type but exceeds the maximum limit of the VARCHAR2 type, OceanBase Database will truncate the result and return it. However, no prompt will be displayed on the client.
Notice
If the return value is of the CLOB type and its length exceeds the maximum limit, OceanBase Database does not return the data and displays an error message.
| Function | Description |
|---|---|
| CHR | Converts an ASCII value to a character. |
| CONCAT | Concatenates two strings. |
| INITCAP | Capitalizes the first letter of each word in a string. |
| LOWER | Converts a string to lowercase. |
| LPAD | Pads the specified character on the left side of a string. |
| LTRIM | Removes leading spaces or the specified character from a string. |
| NLS_LOWER | Converts a string to lowercase using the specified language environment. |
| NLS_UPPER | Converts a string to uppercase using the specified language environment. |
| NLSSORT | Returns the sort key of a string. |
| REGEXP_REPLACE | Replaces characters in a string using a regular expression. |
| REGEXP_SUBSTR | Extracts a substring from a string using a regular expression. |
| REPLACE | Replaces a substring in a string. |
| RPAD | Pads the specified character on the right side of a string. |
| RTRIM | Removes trailing spaces or the specified character from a string. |
| SUBSTR | Extracts a substring from a string. |
| TRIM | Removes leading and trailing spaces or the specified character from a string. |
| TRANSLATE | Replaces characters in a string with other characters. |
| UPPER | Converts a string to uppercase. |
String functions that return numbers
String functions that return numbers return numbers based on specified conditions, such as ASCII values or the positions of characters (strings).
| Function | Description |
|---|---|
| ASCII | Returns the ASCII value of the first character in a string. |
| INSTR | Returns the position of a substring in a string. |
| LENGTH | Returns the length of a string. |
| REGEXP_COUNT | Returns the number of matches for a regular expression in a string. |
| REGEXP_INSTR | Returns the position of a regular expression match. |
Date and time functions
The date and time functions support three types of input parameters: date and time (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH).
The following functions support only the DATE data type as an input parameter: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.
If you provide a TIMESTAMP type parameter to the preceding functions, OceanBase Database implicitly converts the TIMESTAMP value to the DATE type before passing it to the function for calculation and returns a DATE value.
Before using date and time functions, we recommend that you execute the SELECT * FROM NLS_DATABASE_PARAMETERS statement to view the current NLS format, as shown in the following example:
obclient> SELECT * FROM NLS_DATABASE_PARAMETERS;
+-------------------------+------------------------------+
| PARAMETER | VALUE |
+-------------------------+------------------------------+
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_TIMESTAMP_FORMAT | YYYY-MM-DD HH24:MI:SS |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TERRITORY | AMERICA |
| NLS_SORT | BINARY |
| NLS_COMP | BINARY |
| NLS_CHARACTERSET | AL32UTF8 |
| NLS_NCHAR_CHARACTERSET | AL16UTF16 |
| NLS_DATE_LANGUAGE | AMERICAN |
| NLS_LENGTH_SEMANTICS | BYTE |
| NLS_NCHAR_CONV_EXCP | FALSE |
| NLS_CALENDAR | GREGORIAN |
| NLS_NUMERIC_CHARACTERS | ., |
+-------------------------+------------------------------+
13 rows in set
If the display format is inconsistent with that in the example, you can execute the following commands to change the format:
obclient>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected
obclient>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected
/*Set the number of decimal places of seconds to 9 in the return value.*/
obclient> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
Query OK, 0 rows affected
Notice
MONTHS_BETWEENreturns a single number.ROUNDandTRUNCcannot perform implicit conversion. You must pass aDATEvalue. Otherwise, an error will be returned.
The remaining functions support all three parameter data types and return values of the same data type as the input parameters.
| Function | Description |
|---|---|
| ADD_MONTHS | Adds a specified number of months to a date. |
| CURRENT_DATE | Returns the current date. |
| CURRENT_TIMESTAMP | Returns the current date and time. |
| DBTIMEZONE | Returns the database time zone. |
| EXTRACT | Extracts a specified part from a date or timestamp. |
| FROM_TZ | Converts a timestamp and time zone to a timestamp with time zone. |
| LAST_DAY | Returns the last day of the month in which a specified date falls. |
| LOCALTIMESTAMP | Returns the current local timestamp. |
| MONTHS_BETWEEN | Returns the number of months between two dates. |
| NEXT_DAY | Returns the date of the next specified weekday after a specified date. |
| NUMTODSINTERVAL | Converts a number to an interval from day to second. |
| NUMTOYMINTERVAL | Converts a number to an interval from year to month. |
| ROUND | Rounds a date to a specified format. |
| SESSIONTIMEZONE | Returns the time zone of the current session. |
| SYSDATE | Returns the current system date and time. |
| SYSTIMESTAMP | Returns the current system timestamp. |
| SYS_EXTRACT_UTC | Extracts the UTC time from a timestamp with time zone. |
| TO_CHAR | Converts a date or timestamp to a string. |
| TO_DSINTERVAL | Converts a string to an interval from day to second. |
| TO_TIMESTAMP | Converts a string to a timestamp. |
| TO_TIMESTAMP_TZ | Converts a string to a timestamp with time zone. |
| TO_YMINTERVAL | Converts a string to an interval from year to month. |
| TRUNC | Truncates a date to a specified format. |
| TZ_OFFSET | Returns the offset of a specified time zone. |
General comparison functions
You can use the functions in this category to quickly find the maximum and minimum values in a collection.
| Function | Description |
|---|---|
| GREATEST | Returns the maximum value from a list of parameters. |
| LEAST | Returns the minimum value from a list of parameters. |
Conversion functions
You can use the functions in this category to convert data from one data type to another.
| Function | Description |
|---|---|
| ASCIISTR | Converts a string to an ASCII string. |
| CAST | Converts an expression to a specified data type. |
| CHARTOROWID | Converts a string to a ROWID. |
| CONVERT | Converts a string from one character set to another. |
| HEXTORAW | Converts a hexadecimal string to RAW data. |
| RAWTOHEX | Converts RAW data to a hexadecimal string. |
| ROWIDTOCHAR | Converts a ROWID to a string. |
| ROWIDTONCHAR | Converts a ROWID to an NCHAR string. |
| SCN_TO_TIMESTAMP | Converts an SCN to a timestamp. |
| TIMESTAMP_TO_SCN | Converts a timestamp to an SCN. |
| TO_BINARY_DOUBLE | Converts an expression to a BINARY_DOUBLE. |
| TO_BINARY_FLOAT | Converts an expression to a BINARY_FLOAT. |
| TO_BLOB | Converts an expression to a BLOB. |
| TO_CHAR | Converts an expression to a string. |
| TO_CLOB | Converts an expression to a CLOB. |
| TO_DATE | Converts a string to a date. |
| TO_MULTI_BYTE | Converts a single-byte character to a multibyte character. |
| TO_NCHAR | Converts an expression to an NCHAR string. |
| TO_NUMBER | Converts an expression to a number. |
| TO_SINGLE_BYTE | Converts a multibyte character to a single-byte character. |
| TO_YMINTERVAL | Converts a string to an interval from year to month. |
| TO_DSINTERVAL | Converts a string to an interval from day to second. |
| TO_TIMESTAMP | Converts a string to a timestamp. |
| TO_TIMESTAMP_TZ | Converts a string to a timestamp with time zone. |
| UNISTR | Converts a string to a Unicode string. |
Encoding and decoding functions
You can use the functions in this category to encode and decode data in OceanBase Database.
| Function | Description |
|---|---|
| DECODE | A conditional expression similar to a CASE statement. |
| DUMP | Returns the internal representation of an expression. |
| ORA_HASH | Calculates the hash value of an expression. |
| VSIZE | Returns the number of bytes in the internal representation of an expression. |
Null-related functions
Null-related functions are used to handle null values in parameters. For most functions, if the input parameter is a null value (NULL), the result is also NULL. In such cases, you can use the NVL function to return a non-null value. For example, if the commission_pct column in a table that records commissions is a null value (NULL), the expression NVL(commission_pct, 0) returns 0. If the commission_pct value is not NULL, the actual commission value is returned.
| Function | Description |
|---|---|
| COALESCE | Returns the first non-NULL value |
| LNNVL | Checks whether the condition is NULL or FALSE |
| NANVL | If the first parameter is NaN, returns the second parameter |
| NULLIF | If the two parameters are equal, returns NULL |
| NVL | If the first parameter is NULL, returns the second parameter |
| NVL2 | If the first parameter is not NULL, returns the second parameter; otherwise, returns the third parameter |
Environment-related functions
Environment-related functions provide session or tenant instance-related environment information.
| Function | Description |
|---|---|
| OB_VERSION | Returns the version of OceanBase Database |
| SYS_CONTEXT | Returns the attribute value of the specified context |
| SYS_GUID | Generates a globally unique identifier |
| UID | Returns the user ID of the current user |
| USER | Returns the username |
| USERENV | Returns the environment information of the current session |
Hierarchical functions
| Function | Description |
|---|---|
| SYS_CONNECT_BY_PATH | Returns the path from the root to the node, with column values from each row returned by the CONNECT BY condition separated by the specified delimiter. |
JSON functions
JSON functions create, operate on, and search JSON values.
| Function | Description |
|---|---|
| JSON_ARRAY | Creates a JSON array |
| JSON_ARRAYAGG | Aggregates multiple rows into a JSON array |
| JSON_EQUAL | Compares whether two JSON values are equal |
| JSON_EXISTS | Checks whether a specified path exists in a JSON document |
| JSON_MERGEPATCH | Merges documents using JSON Merge Patch |
| JSON_OBJECT | Creates a JSON object |
| JSON_OBJECTAGG | Aggregates multiple rows into a JSON object |
| JSON_QUERY | Queries a value from a JSON document |
| JSON_TABLE | Converts JSON data into a table format |
| JSON_VALUE | Extracts a scalar value from a JSON document |
XML functions
XML functions efficiently query, update, and search XML documents.
| Function | Description |
|---|---|
| DELETEXML | Deletes a node from an XML document |
| EXISTSNODE | Checks whether a specified node exists in an XML document |
| EXTRACT | Extracts a node from an XML document |
| EXTRACTVALUE | Extracts a value from an XML document |
| INSERTCHILDXML | Inserts a child node into an XML document |
| UPDATEXML | Updates a node in an XML document |
| XMLAGG | Aggregates multiple XML values into a single XML document |
| XMLATTRIBUTES | Creates an XML attribute |
| XMLCAST | Converts an XML value to a specified data type |
| XMLCONCAT | Concatenates multiple XML values |
| XMLELEMENT | Creates an XML element |
| XMLFOREST | Creates an XML forest |
| XMLPARSE | Parses a string into an XML document |
| XMLSEQUENCE | Converts an XML document into a sequence of rows |
| XMLSERIALIZE | Serializes an XML document into a string |
| XMLTABLE | Converts XML data into a table format |
Aggregate functions
Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause in a SELECT statement.
| Function | Description |
|---|---|
| APPROX_COUNT_DISTINCT | Returns the approximate count of distinct values |
| AVG | Returns the average of numeric values |
| CORR | Returns the correlation coefficient of two numeric columns |
| COUNT | Returns the number of rows or non-NULL values |
| COVAR_POP | Returns the population covariance |
| COVAR_SAMP | Returns the sample covariance |
| CUME_DIST | Returns the cumulative distribution value |
| DENSE_RANK | Returns the dense rank |
| GROUP_ID | Returns the group ID |
| GROUPING | Returns the grouping information of the GROUP BY clause |
| GROUPING_ID | Returns the bit vector of the group ID |
| KEEP | Retains values that meet the specified condition in the group |
| LISTAGG | Concatenates multiple row values into a string |
| MAX | Returns the maximum value |
| MEDIAN | Returns the median |
| MIN | Returns the minimum value |
| PERCENTILE_CONT | Returns the continuous percentile value |
| PERCENTILE_DISC | Returns the discrete percentile value |
| PERCENT_RANK | Returns the percentile rank |
| RANK | Returns the rank |
| REGR_ | A family of linear regression functions |
| ROLLUP | Creates group aggregates |
| STDDEV | Returns the standard deviation |
| STDDEV_POP | Returns the population standard deviation |
| STDDEV_SAMP | Returns the sample standard deviation |
| SUM | Returns the sum of numeric values |
| VAR_POP | Returns the population variance |
| VAR_SAMP | Returns the sample variance |
| VARIANCE | Returns the variance |
| WMSYS.WM_CONCAT/WM_CONCAT | Concatenates multiple row values into a string |
Analytic functions
Analytic functions (also known as window functions in some databases) are similar to aggregate functions in that they compute values based on a set of rows. However, unlike aggregate functions, which return a single row per group, analytic functions can return multiple rows per group, with each row in the group being the result of a window-based computation. Analytic functions can significantly optimize queries that require self-joins.
| Function | Description |
|---|---|
| ARG_MAX | Returns the argument with the maximum value |
| ARG_MIN | Returns the argument with the minimum value |
| AVG | Returns the average of numeric values |
| CORR | Returns the correlation coefficient between two numeric columns |
| COUNT | Returns the number of rows or non-NULL values |
| COVAR_POP | Returns the population covariance |
| COVAR_SAMP | Returns the sample covariance |
| CUME_DIST | Returns the cumulative distribution value |
| DENSE_RANK | Returns the dense rank |
| FIRST_VALUE | Returns the first value in the window |
| KEEP | Retains specified values in groups |
| LAG | Returns the value from the previous row or previous N rows |
| LAST_VALUE | Returns the last value in the window |
| LEAD | Returns the value from the next row or next N rows |
| LISTAGG | Concatenates values from multiple rows into a single string |
| MAX | Returns the maximum value |
| MEDIAN | Returns the median |
| MIN | Returns the minimum value |
| NTH_VALUE | Returns the Nth value in the window |
| NTILE | Divides the result set into a specified number of groups |
| PERCENTILE_CONT | Returns a continuous percentile value |
| PERCENTILE_DISC | Returns a discrete percentile value |
| PERCENT_RANK | Returns the percentile rank |
| RANK | Returns the rank |
| RATIO_TO_REPORT | Returns the proportion of a value within a group |
| REGR_ | Family of linear regression functions |
| ROW_NUMBER | Returns the row number |
| STDDEV | Returns the standard deviation |
| STDDEV_POP | Returns the population standard deviation |
| STDDEV_SAMP | Returns the sample standard deviation |
| SUM | Returns the sum of numeric values |
| VAR_POP | Returns the population variance |
| VAR_SAMP | Returns the sample variance |
| VARIANCE | Returns the variance |
| WMSYS.WM_CONCAT/WM_CONCAT | Concatenates values from multiple rows into a single string |
Information functions
| Function | Description |
|---|---|
| OB_TRANSACTION_ID | Returns the current transaction ID |
Spatial functions
| Function | Description |
|---|---|
| SDO_GEOMETRY | Creates a spatial geometry object |
| Query and compute functions | Queries and computes the properties of spatial geometry objects |