Overview of functions

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 parameter 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 form. Functions can have a variable number of parameters, ranging from one to several.

FunctionName(Parameter, Parameter, ...)

Functions can also have no parameters, similar to pseudo-columns. However, pseudo-columns typically return different values for each row in the result set, while functions without parameters usually return the same value for each row.

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

Analytic functions and aggregate functions both perform aggregation calculations on a set of rows (a group of rows). The difference is that aggregate functions return one value (one row) per group, while analytic functions can return multiple values (multiple rows) per group. A set 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 analytic functions, the special keyword OVER is required to specify the window. For more information about window functions, see Analytic functions.

Numeric functions

The input and output of numeric functions are numeric types. Most numeric functions return values 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.

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 the actual size of the return value exceeds the maximum limit of the VARCHAR2 type, OceanBase Database truncates the result and returns it. However, no prompt is displayed on the client.

String functions that return numeric values

String functions that return numeric values return numeric values based on specified conditions, such as ASCII values and the positions of characters (strings).

Date and time functions

The date and time functions support three types of input data: 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 only support the DATE data type as input: ADD_MONTHS, CURRENT_DATE, LAST_DAY, and NEXT_DAY.

If you provide a TIMESTAMP type as input to the above functions, OceanBase Database will implicitly convert it to the DATE type before passing it to the function for calculation and will return a DATE type result.

Before using date and time functions, we recommend that you execute SELECT * FROM NLS_DATABASE_PARAMETERS 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 in the example is different from the one in the text, 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 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

The remaining functions support all three parameter data types and return values of the same data type as the input parameters.

General comparison functions

You can use the functions in this category to quickly find the maximum and minimum values in a collection.

Conversion functions

You can use the functions in this category to convert data from one data type to another.

Encoding and decoding functions

You can use the functions in this category to encode and decode data in OceanBase Database.

Null value-related functions

Null value-related functions are used to process null values in parameters. For most functions, if the input parameter is a null value (NULL), the output is also NULL. In this case, 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.

Environment-related functions

Environment-related functions provide session or tenant instance-related environment information.

Hierarchical functions

JSON functions

JSON functions create, operate on, and search for JSON values.

XML functions

XML functions efficiently query, update, and search XML documents.

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 SELECT statements.

Analytic functions

Analytic functions (also known as window functions in some databases) are similar to aggregate functions, as they compute values based on a set of rows. However, while aggregate functions return only one row per group, analytic functions can return multiple rows per group, with each row's value calculated based on the window's logic. Analytic functions can significantly optimize queries that require self-joins.

Information functions

Spatial functions