Overview|V4.4.2| docs|Distributed Database

Overview

Last Updated：2026-03-06 07:02:42 Updated

This chapter describes the built-in functions of OceanBase Database in MySQL-compatible mode.

Single-row functions

Single-row functions are functions that return a single row of result. They include date and time functions, string functions, conversion functions, mathematical functions, comparison functions, and flow control functions.

Date and time functions

Function	Description
ADDDATE	Adds a specified number of days to a date
ADDTIME	Adds a specified amount of time to a time
CONVERT_TZ	Converts a time from one time zone to another
CURDATE	Returns the current date
CURRENT_DATE	Returns the current date
CURRENT_TIME	Returns the current time
CURRENT_TIMESTAMP	Returns the current date and time
CURTIME	Returns the current time
DATE	Extracts the date part from a datetime expression
DATE_ADD	Adds a specified time interval to a date
DATE_FORMAT	Formats a date according to a specified format
DATE_SUB	Subtracts a specified time interval from a date
DATEDIFF	Returns the number of days between two dates
DAY	Extracts the day from a date
DAYNAME	Returns the name of the day of the week for a date
DAYOFMONTH	Returns the day of the month for a date
DAYOFWEEK	Returns the day of the week for a date (1=Sunday)
DAYOFYEAR	Returns the day of the year for a date
EXTRACT	Extracts a specified part from a datetime
FROM_DAYS	Converts a number of days to a date
FROM_UNIXTIME	Converts a Unix timestamp to a datetime
GET_FORMAT	Returns a datetime format string in a specified format
HOUR	Extracts the hour from a time
LAST_DAY	Returns the last day of the month for a specified date
LOCALTIME	Returns the current local time
LOCALTIMESTAMP	Returns the current local timestamp
MAKEDATE	Creates a date from a year and a day
MAKETIME	Creates a time from hours, minutes, and seconds
MICROSECOND	Extracts the microseconds from a time
MINUTE	Extracts the minute from a time
MONTH	Extracts the month from a date
MONTHNAME	Returns the name of the month for a date
NOW	Returns the current date and time
PERIOD_ADD	Adds a specified number of months to a period
PERIOD_DIFF	Returns the number of months between two periods
QUARTER	Returns the quarter for a date
SECOND	Extracts the second from a time
SEC_TO_TIME	Converts a number of seconds to a time
STR_TO_DATE	Converts a string to a date
SUBDATE	Subtracts a specified time interval from a date
SUBTIME	Subtracts a specified amount of time from a time
SYSDATE	Returns the current system date and time
TIME	Extracts the time part from a datetime expression
TIME_FORMAT	Formats a time according to a specified format
TIMEDIFF	Returns the difference between two times
TIMESTAMP	Converts an expression to a timestamp
TIMESTAMPADD	Adds a specified time interval to a timestamp
TIMESTAMPDIFF	Returns the difference between two timestamps
TIME_TO_SEC	Converts a time to a number of seconds
TIME_TO_USEC	Converts a time to a number of microseconds
TO_DAYS	Converts a date to a number of days
TO_SECONDS	Converts a datetime to a number of seconds
UNIX_TIMESTAMP	Converts a datetime to a Unix timestamp
USEC_TO_TIME	Converts a number of microseconds to a time
UTC_DATE	Returns the current UTC date
UTC_TIME	Returns the current UTC time
UTC_TIMESTAMP	Returns the current UTC timestamp
WEEK	Returns the week number for a date
WEEKDAY	Returns the day of the week for a date (0=Monday)
WEEKOFYEAR	Returns the week number for a date in the year
YEAR	Extracts the year from a date
YEARWEEK	Returns the year and week number for a date

String functions

Function	Description
ASCII	Returns the ASCII value of the first character of a string
BIN	Converts a number to a binary string
BIT_LENGTH	Returns the length of a string in bits
CHAR	Converts ASCII values to characters
CHAR_LENGTH	Returns the length of a string in characters
CHARACTER_LENGTH	Returns the length of a string in characters
CONCAT	Concatenates multiple strings
CONCAT_WS	Concatenates multiple strings with a separator
ELT	Returns a string at a specified index
EXPORT_SET	Returns a string containing a specified number of bits
FIELD	Returns the position of a string in a list
FIND_IN_SET	Finds a string in a comma-separated string list
FORMAT	Formats a number to a specified number of decimal places
FROM_BASE64	Decodes a Base64 string
HEX	Converts a string to hexadecimal
INSERT	Inserts a substring into a string
INSTR	Returns the position of a substring in a string
INT2IP	Converts an integer to an IP address
IP2INT	Converts an IP address to an integer
KEYVALUE	Extracts a value from a key-value pair string
LCASE	Converts a string to lowercase
LEFT	Returns a specified number of characters from the left of a string
LENGTH	Returns the length of a string in bytes
LOCATE	Returns the position of a substring in a string
LOWER	Converts a string to lowercase
LPAD	Pads a string with a specified character on the left
LTRIM	Removes leading spaces from a string
MAKE_SET	Creates a string set from bit values
MID	Extracts a substring from a string
OCT	Converts a number to an octal string
OCTET_LENGTH	Returns the length of a string in bytes
ORD	Returns the character set value of the first character of a string
POSITION	Returns the position of a substring in a string
QUOTE	Wraps a string in quotes and escapes special characters
REGEXP	Tests whether a string matches a regular expression
REGEXP_INSTR	Returns the position of a regular expression match
REGEXP_LIKE	Tests whether a string matches a regular expression
REGEXP_REPLACE	Replaces a string using a regular expression
REGEXP_SUBSTR	Extracts a substring using a regular expression
REPEAT	Repeats a string a specified number of times
REPLACE	Replaces a substring in a string
REVERSE	Reverses a string
RIGHT	Returns a specified number of characters from the right of a string
RLIKE	Tests whether a string matches a regular expression
RPAD	Pads a string with a specified character on the right
RTRIM	Removes trailing spaces from a string
SOUNDEX	Returns the SOUNDEX value of a string
SPACE	Returns a string of a specified number of spaces
SPLIT_PART	Splits a string by a separator and returns a specified part
STRCMP	Compares two strings
SUBSTR	Extracts a substring from a string
SUBSTRING	Extracts a substring from a string
SUBSTRING_INDEX	Extracts a substring from a string by a separator
TO_BASE64	Encodes a string as Base64
TRIM	Removes leading and trailing spaces or specified characters from a string
UCASE	Converts a string to uppercase
UNHEX	Converts a hexadecimal string to characters
UPPER	Converts a string to uppercase
URL_DECODE	Decodes a URL-encoded string
URL_ENCODE	Encodes a string as URL format
WEIGHT_STRING	Returns the weight string of a string

Conversion functions

Function	Description
CAST	Converts an expression to the specified data type
CONVERT	Converts an expression to the specified character set
DECODE_TRACE_ID	Decodes a trace ID
SCN_TO_TIMESTAMP	Converts a SCN to a timestamp
TIMESTAMP_TO_SCN	Converts a timestamp to a SCN
TO_CHAR(character)	Converts a character expression to a string
TO_CHAR(datetime)	Converts a datetime expression to a string
TO_CHAR(number)	Converts a numeric expression to a string

Mathematical functions

Function	Description
ABS	Returns the absolute value (positive value) of a specified numeric expression
ACOS	Returns the angle in radians whose cosine is the specified numeric expression. This function is also known as the inverse cosine
ASIN	Returns the angle in radians whose sine is the specified numeric expression. This function is also known as the inverse sine
ATAN	Returns the angle in radians whose tangent is the specified numeric expression. This function is also known as the inverse tangent
ATAN2	Returns the angle in radians whose tangent is the ratio of the two specified numeric expressions
BIT_COUNT	Returns the number of 1s in the binary representation of a specified numeric value
CEIL	Returns the smallest integer that is greater than or equal to the specified numeric value
CEILING	Returns the smallest integer that is greater than or equal to the specified numeric value
CONV	Converts a numeric value from one base to another
COS	Returns the cosine of a specified angle
COT	Returns the cotangent of a specified angle
CRC32	Calculates a cyclic redundancy check (CRC) value
DEGREES	Converts radians to degrees
EXP	Returns e raised to the specified power
FLOOR	Returns the largest integer that is less than or equal to the specified numeric value
GENERATOR	Generates a random number in the specified range
LN	Returns the natural logarithm of a specified numeric value
LOG	Returns the logarithm of a specified numeric value
LOG10	Returns the logarithm of a specified numeric value with base 10
LOG2	Returns the logarithm of a specified numeric value with base 2
MOD	Returns the remainder of dividing one numeric value by another
NEG	Returns the negative of a specified numeric value
NORMAL	Generates a random number that follows a normal distribution
PI	Returns the value of π
POWER	Returns a specified numeric value raised to the specified power
POW	Returns a specified numeric value raised to the specified power
RADIANS	Converts degrees to radians
RAND	Generates a random number between 0 and 1
RANDOM	Generates a random number between 0 and 1
RANDSTR	Generates a random string of the specified length
ROUND	Rounds a numeric value to the specified number of decimal places
SIGN	Returns the sign of a numeric value (-1, 0, or 1)
SIN	Returns the sine of a specified angle
SQRT	Returns the square root of a specified numeric value
TAN	Returns the tangent of a specified angle
TRUNCATE	Truncates a numeric value to the specified number of decimal places
UNIFORM	Generates a uniformly distributed random number in the specified range
ZIPF	Generates a random number that follows a Zipf distribution

Comparison functions

Function	Description
GREATEST	Returns the maximum value from the list of parameters
INTERVAL	Returns the index of the largest parameter that is less than the first parameter
ISNULL	Tests whether an expression is NULL
LEAST	Returns the minimum value from the list of parameters

Control flow functions

Function	Description
CASE	A conditional expression that returns different values based on the condition
IF	A conditional function that returns different values based on the condition
IFNULL	If the first parameter is NULL, returns the second parameter
NULLIF	If the two parameters are equal, returns NULL
ORA_DECODE	An Oracle-compatible DECODE function

Aggregate functions

An aggregate function performs a calculation on a set of values and returns a single value. An aggregate function ignores null values. Aggregate functions are often used with the GROUP BY clause of a SELECT statement.

All aggregate functions are deterministic. They return the same value when given the same set of input values at any time.

In OceanBase Database, only one value expression can be used in an aggregate function. For example, COUNT(c1, c2) is not supported, but COUNT(c1) is supported.

Function	Description
APPROX_COUNT_DISTINCT	Returns the approximate number of distinct values.
ARG_MAX	Returns the parameter with the maximum value.
ARG_MIN	Returns the parameter with the minimum value.
AVG	Returns the average value of the numeric values.
BIT_AND	Performs a bitwise AND operation on the numeric values.
BIT_OR	Performs a bitwise OR operation on the numeric values.
BIT_XOR	Performs a bitwise XOR operation on the numeric values.
COUNT	Returns the number of rows or the number of non-NULL values.
GROUP_CONCAT	Concatenates values from multiple rows into a string.
GROUPING	Returns the grouping information of the `GROUP BY` clause.
MAX	Returns the maximum value.
MIN	Returns the minimum value.
STD	Returns the standard deviation.
STDDEV	Returns the standard deviation.
STDDEV_POP	Returns the population standard deviation.
STDDEV_SAMP	Returns the sample standard deviation.
SUM	Returns the sum of the numeric values.
VAR_POP	Returns the population variance.
VAR_SAMP	Returns the sample variance.
VARIANCE	Returns the variance.

Analytic functions

Analytic functions (also known as window functions in some databases) are similar to aggregate functions in that they calculate based on a set of rows. However, unlike aggregate functions, which return only one row per group, analytic functions can return multiple rows per group, with each row in the group being the result of a window-based calculation. Analytic functions can significantly optimize queries that require self-joins.

A "window" is also known as a frame. OceanBase Database supports both ROWS and RANGE frame semantics. The ROWS frame is based on physical row offsets, while the RANGE frame is based on logical value offsets.

Function	Description
APPROX_COUNT_DISTINCT	Returns the approximate count of distinct values.
AVG	Returns the average of the values.
BIT_AND	Performs a bitwise AND operation on the values.
BIT_OR	Performs a bitwise OR operation on the values.
BIT_XOR	Performs a bitwise XOR operation on the values.
COUNT	Returns the number of rows or non-NULL values.
CUME_DIST	Returns the cumulative distribution value.
DENSE_RANK	Returns the dense rank.
FIRST_VALUE	Returns the first value in the window.
LAG	Returns the value from the previous row or the previous N rows.
LAST_VALUE	Returns the last value in the window.
LEAD	Returns the value from the next row or the next N rows.
MAX	Returns the maximum value.
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.
PERCENT_RANK	Returns the percentile rank.
PERCENTILE_CONT	Returns the continuous percentile value.
RANK	Returns the rank.
ROW_NUMBER	Returns the row number.
STD	Returns the standard deviation.
STDDEV	Returns the standard deviation.
STDDEV_POP	Returns the population standard deviation.
STDDEV_SAMP	Returns the sample standard deviation.
SUM	Returns the sum of the values.
VAR_POP	Returns the population variance.
VAR_SAMP	Returns the sample variance.
VARIANCE	Returns the variance.

Encryption functions

Encryption functions are used for data encryption and decryption. They include encryption and decryption functions.

Function	Description
AES_DECRYPT	Decrypts data using the AES algorithm
AES_ENCRYPT	Encrypts data using the AES algorithm
COMPRESS	Compresses a string
DECODE	Decodes a string
DES_DECRYPT	Decrypts data using the DES algorithm
DES_ENCRYPT	Encrypts data using the DES algorithm
ENCODE	Encodes a string
ENCRYPT	Encrypts a string
ENHANCED_AES_DECRYPT	Decrypts data using the enhanced AES algorithm
ENHANCED_AES_ENCRYPT	Encrypts data using the enhanced AES algorithm
MD5	Calculates the MD5 hash value
PASSWORD	Calculates the password hash value
RANDOM_BYTES	Generates random bytes
SHA	Calculates the SHA hash value
SHA1	Calculates the SHA1 hash value
SHA2	Calculates the SHA2 hash value
SM3	Calculates the SM3 hash value
SM4_DECRYPT	Decrypts data using the SM4 algorithm
SM4_ENCRYPT	Encrypts data using the SM4 algorithm
UNCOMPRESS	Decompresses a string
UNCOMPRESSED_LENGTH	Returns the length of the decompressed string
VALIDATE_PASSWORD_STRENGTH	Validates the strength of a password

Information functions

Information functions return specified system information.

Function	Description
BENCHMARK	Executes an expression the specified number of times and returns the execution time.
CHARSET	Returns the character set of a string.
COLLATION	Returns the collation of a string.
CONNECTION_ID	Returns the ID of the current connection.
COERCIBILITY	Returns the coercibility value of a string.
CURRENT_ROLE	Returns the current role.
CURRENT_USER	Returns the current user.
DATABASE	Returns the name of the current database.
FOUND_ROWS	Returns the number of rows affected by the last SELECT statement.
ICU_VERSION	Returns the version of the ICU library.
LAST_INSERT_ID	Returns the ID of the last inserted row.
OB_TRANSACTION_ID	Returns the ID of the current transaction.
OB_VERSION	Returns the version of OceanBase Database.
ROW_COUNT	Returns the number of rows affected by the last statement.
SCHEMA	Returns the name of the current database.
SESSION_USER	Returns the current session user.
SYSTEM_USER	Returns the system user.
USER	Returns the current user.
VERSION	Returns the database version.

JSON functions

JSON functions are used to create, manipulate, and search JSON values.

Function	Description
JSON_ARRAY	Creates a JSON array
JSON_ARRAY_APPEND	Appends a value to a JSON array
JSON_ARRAY_INSERT	Inserts a value into a JSON array
JSON_ARRAYAGG	Aggregates multiple rows into a JSON array
JSON_CONTAINS	Checks whether a JSON document contains a specified value
JSON_CONTAINS_PATH	Checks whether a JSON document contains a specified path
JSON_DEPTH	Returns the depth of a JSON document
JSON_EXTRACT	Extracts a value from a JSON document
JSON_INSERT	Inserts a value into a JSON document
JSON_KEYS	Returns all keys of a JSON object
JSON_LENGTH	Returns the length of a JSON array or object
JSON_MERGE	Merges JSON documents
JSON_MERGE_PATCH	Merges documents using JSON Merge Patch
JSON_MERGE_PRESERVE	Merges JSON documents while preserving duplicate keys
JSON_OBJECT	Creates a JSON object
JSON_OBJECTAGG	Aggregates multiple rows into a JSON object
JSON_OVERLAPS	Checks whether two JSON documents overlap
JSON_PRETTY	Formats a JSON document
JSON_QUOTE	Converts a string to a JSON string
JSON_REMOVE	Removes a value from a JSON document
JSON_REPLACE	Replaces a value in a JSON document
JSON_SCHEMA_VALID	Validates whether a JSON document conforms to a schema
JSON_SCHEMA_VALIDATION_REPORT	Returns a JSON schema validation report
JSON_SEARCH	Searches for a value in a JSON document
JSON_SET	Sets a value in a JSON document
JSON_STORAGE_SIZE	Returns the storage size of a JSON document
JSON_TABLE	Converts JSON data to a table format
JSON_TYPE	Returns the type of a JSON value
JSON_UNQUOTE	Removes quotes from a JSON string
JSON_VALID	Validates whether a JSON document is valid
JSON_VALUE	Extracts a scalar value from a JSON document
MEMBER	Checks whether a value is a member of a JSON array

XML functions

XML functions are used to process and analyze XML data.

Function	Description
EXTRACTVALUE	Extracts a value from an XML document.
UPDATEXML	Updates a value in an XML document.

Spatial functions

Spatial functions are used to process and analyze spatial data. Spatial functions can be used with geospatial data.

Function	Description
_ST_GeoHash	Calculates the GeoHash value of a geometry object.
Spatial constructor functions	Create spatial geometry objects.
Spatial relationship functions	Test the relationships between spatial objects.
Geometry processing functions	Process spatial geometry objects.
Geometry format conversion functions	Convert the format of geometry objects.
Geometry attribute functions	Retrieve the attributes of geometry objects.
Spatial format conversion functions	Convert the format of spatial data.
Spatial analysis functions	Analyze spatial data.
Spatial operation functions	Perform operations on spatial objects.
Spatial accessor functions	Access the attributes of spatial objects.
Geometry value creation functions	Create geometry values.

Lock functions

Lock functions are used to manipulate locks.

Function	Description
GET_LOCK	Acquires a named lock.
IS_FREE_LOCK	Checks whether a named lock is available.
IS_USED_LOCK	Checks whether a named lock is in use.
RELEASE_ALL_LOCKS	Releases all named locks.
RELEASE_LOCK	Releases a named lock.

Bitmap functions

Bitmap functions are used to process efficiently compressed bitmap data.

Function	Description
Bitmap constructor	Creates a bitmap object
Bitmap operator	Operates on a bitmap object
Bitmap operator	Performs operations on a bitmap
Bitmap cardinality calculation	Calculates the cardinality of a bitmap
Bitmap attribute judgment	Judges the attributes of a bitmap
Bitmap output	Outputs the data of a bitmap
Bitmap aggregation	Aggregates the data of a bitmap

Array functions

Array functions are used to process and analyze array data.

Function	Description
Array constructor	Creates an array object
Array manipulation function	Manipulates an array object
Array output function	Outputs array data
Array judgment function	Judges array attributes

Mapping functions

Function	Description
Mapping constructor	Creates a mapping object.
Mapping operation function	Performs operations on a mapping object.
Mapping property check function	Checks mapping properties.

Audit functions

Function	Description
AUDIT_LOG_FILTER_REMOVE_FILTER	Removes an audit log filter
AUDIT_LOG_FILTER_REMOVE_USER	Removes an audit log user filter
AUDIT_LOG_FILTER_SET_FILTER	Sets an audit log filter
AUDIT_LOG_FILTER_SET_USER	Sets an audit log user filter

Performance mode functions

Function	Description
FORMAT_BYTES	Formats the number of bytes.
FORMAT_PICO_TIME	Formats the number of picoseconds.

Text processing functions

Function	Description
TOKENIZE	Tokenizes text.

Other functions

Other functions include some that are difficult to classify.

Function	Description
ANY_VALUE	Returns an arbitrary non-NULL value
BIN_TO_UUID	Converts a binary value to a UUID
COALESCE	Returns the first non-NULL value
DEFAULT	Returns the default value of a column
INET6_ATON	Converts an IPv6 address to a binary value
INET6_NTOA	Converts a binary value to an IPv6 address
INET_ATON	Converts an IPv4 address to an integer
INET_NTOA	Converts an integer to an IPv4 address
IS_IPV4	Checks whether a string is a valid IPv4 address
IS_IPV4_COMPAT	Checks whether an IPv6 address is IPv4-compatible
IS_IPV6	Checks whether a string is a valid IPv6 address
IS_UUID	Checks whether a string is a valid UUID
MATCH	Full-text search match function
NAME_CONST	Creates a named constant
NVL	If the first parameter is NULL, returns the second parameter
SLEEP	Pauses execution for the specified number of seconds
UUID	Generates a UUID
UUID_SHORT	Generates a short UUID
UUID_TO_BIN	Converts a UUID to a binary value
VALUES	Returns the values in an INSERT statement