Function overview|V5.0.1|OceanBase Database|docs|OceanBase Documentations

Function overview

Last Updated：2026-09-04 11:28:25 Updated

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

Single-row functions

Single-row functions are primarily functions that return a single row of results, including date and time functions, string functions, conversion functions, mathematical functions, comparison functions, and flow control functions.

Date and time functions

Function name	Feature description
ADDDATE	Add a specified number of days to a date
ADDTIME	Add a specified amount of time to a time
CONVERT_TZ	Convert time from one timezone to another
CURDATE	Return the current date
CURRENT_DATE	Return the current date
CURRENT_TIME	Return the current time.
CURRENT_TIMESTAMP	Return the current date and time
CURTIME	Return the current time.
DATE	Extract the date part from a datetime expression
DATE_ADD	Add a specified time interval to a date
DATE_FORMAT	Format a date in the specified format
DATE_SUB	Subtract a specified time interval from a date
DATE_TRUNC	Truncate date and time to the specified unit (Doris/ADB/PostgreSQL compatible semantics)
DATEDIFF	Return the number of days between two dates
DAY	Extract days from a date
DAYNAME	Return the weekday name of the date.
DAYOFMONTH	Return the number of days in a date
DAYOFWEEK	Return the day of the week for a date (1=Sunday).
DAYOFYEAR	Return the number of days in a year for a given date.
EXTRACT	Extract a specified part from a date and time
FROM_DAYS	Convert days to date
FROM_UNIXTIME	Converts a Unix timestamp to a date and time
GET_FORMAT	Return a date and time string in the specified format.
HOUR	Extract hours from a time
LAST_DAY	Return the last day of the month containing the specified date.
LOCALTIME	Return the current local time.
LOCALTIMESTAMP	Return the current local timestamp.
MAKEDATE	Create a date based on the year and day
MAKETIME	Create a time based on hours, minutes, and seconds
MICROSECOND	Extract microseconds from time
MINUTE	Extract Minutes from Time
MONTH	Extract month from date
MONTHNAME	Return the name of the month for a date.
NOW	Return the current date and time
PERIOD_ADD	Add the specified number of months to the period
PERIOD_DIFF	Return the number of months between two periods.
QUARTER	Quarter of the return date
SECOND	Extract seconds from time
SEC_TO_TIME	Convert seconds to time
STR_TO_DATE	Convert String to Date
SUBDATE	Subtract a specified time interval from a date
SUBTIME	Subtract the specified time from the current time
SYSDATE	Return the current system date and time.
TIME	Extract the time part from a datetime expression
TIME_FORMAT	Format time in the specified format
TIMEDIFF	Return the difference between two times
TIMESTAMP	Convert an expression to a timestamp
TIMESTAMPADD	Add a specified time interval to a timestamp
TIMESTAMPDIFF	Return the difference between two timestamps.
TIME_TO_SEC	Convert time to seconds
TIME_TO_USEC	Convert time to microseconds
TO_DAYS	Convert date to day
TO_SECONDS	Convert datetime to seconds
UNIX_TIMESTAMP	Convert date and time to Unix timestamp
USEC_TO_TIME	Convert microseconds to time
UTC_DATE	Return the current UTC date.
UTC_TIME	Return the current UTC time.
UTC_TIMESTAMP	Return the current UTC timestamp.
WEEK	Return the week number of the date.
WEEKDAY	Return the day of the week for the date (0=Monday).
WEEKOFYEAR	Week of the year
YEAR	Extract year from date
YEARWEEK	Return the year and week of a date

String functions

Function name	Feature description
ASCII	Return the ASCII value of the first character in a string.
BIN	Convert a number to a binary string
BIT_LENGTH	Returns the bit length of a string.
CHAR	Convert ASCII values to characters
CHAR_LENGTH	Returns the length of a string.
CHARACTER_LENGTH	Returns the length of a string.
CONCAT	Concatenates multiple strings
CONCAT_WS	Concatenates multiple strings using a delimiter
ELT	Return the string at the specified index
editDistance	Calculate the edit distance between two strings (byte-level, requires a ClickHouse extension function)
editDistanceUTF8	Calculate the Levenshtein distance between two UTF-8 strings (character level, requires a ClickHouse extension function)
EXPORT_SET	Returns a string containing a specified number of bits.
FIELD	Return the index of a string in a list
FIND_IN_SET	Find a string in a comma-separated list of strings
FORMAT	Format a number to a specified number of decimal places
FROM_BASE64	Decode Base64 String
HEX	Convert a string to hexadecimal
INSERT	Insert a substring into a string
INSTR	Return the position of a substring within a string
INT2IP	Convert an integer to an IP address.
IP2INT	Convert IP address to integer
KEYVALUE	Extract Value from Key-Value Pair String
LCASE	Convert a string to lowercase
LEFT	Return the specified number of characters from the left side of the string
LENGTH	Return the byte length of the string.
LOCATE	Return the position of a substring within a string
LOWER	Convert a string to lowercase
LPAD	Pad the left side of a string with specified characters
LTRIM	Delete leading spaces from a string
MAKE_SET	Create a set of strings based on bit values
MAX_PT	Returns the name of the largest physical partition under the given partitioning key for a partitioned table.
MD5_CONCAT_WS	Calculate MD5 after concatenating parameters with separators (MySQL only, at least 3 parameters required)
MID	Extract a substring from a string
OCT	Convert a number to an octal string.
OCTET_LENGTH	Return the byte length of the string.
ORD	Return the character set value of the first character in a string.
POSITION	Return the position of a substring within a string
QUOTE	Enclose a string in quotes and escape special characters
REGEXP	Tests whether a string matches a regular expression.
REGEXP_INSTR	Return the position where the regular expression matches
REGEXP_LIKE	Tests whether a string matches a regular expression.
REGEXP_REPLACE	Replace strings using regular expressions
REGEXP_SUBSTR	Extract Substring Using Regular Expressions
REPEAT	Repeat string for specified number of times
REPLACE	Replace a substring in a string
REVERSE	Reverse a string
RIGHT	Return the specified number of characters from the right side of the string
RLIKE	Tests whether a string matches a regular expression.
RPAD	Pad the right side of a string with specified characters
RTRIM	Remove trailing spaces from a string
SOUNDEX	Return the SOUNDEX value of a string
SPACE	Return a specified number of empty string characters
SPLIT_PART	Split a string by a delimiter and return the specified part
STRCMP	Compare two strings
SUBSTR	Extract a substring from a string
SUBSTRING	Extract a substring from a string
SUBSTRING_INDEX	Extract Substring Based on Separator
TO_BASE64	Encode a string to Base64
TRIM	Remove leading and trailing spaces or specified characters from a string
UCASE	Convert a string to uppercase
UNHEX	Convert Hexadecimal String to Character
UPPER	Convert a string to uppercase
URL_DECODE	Decode a URL-encoded string
URL_ENCODE	Encode a string to URL format
WEIGHT_STRING	Return the weight string of the string

Conversion functions

Function name	Feature description
CAST	Convert an expression to a specified data type.
CONVERT	Convert an expression to a specified character set.
DECODE_TRACE_ID	Decode Tracking ID
SCN_TO_TIMESTAMP	Convert SCN to Timestamp
TIMESTAMP_TO_SCN	Convert timestamp to SCN
TO_CHAR(character)	Convert character expressions to strings
TO_CHAR(datetime)	Convert a date and time expression to a string
TO_CHAR(number)	Convert a numeric expression to a string

Mathematical Functions

Function name	Feature description
ABS	A mathematical function that returns the absolute value (positive number) of a specified numeric expression.
ACOS	Return the angle in radians whose cosine is the specified NUMBER expression, also known as the inverse cosine.
ASIN	Return the angle in radians whose sine is the specified NUMBER expression, also known as arcsine.
ATAN	Returns the angle in radians whose tangent is the specified NUMBER expression, also known as the arctangent.
ATAN2	Returns an angle in radians whose tangent is the ratio of two specified NUMBER expressions.
BIT_COUNT	Return the number of 1s in the binary representation of a specified number
CEIL	Return the smallest integer greater than or equal to a specified value.
CEILING	Return the smallest integer greater than or equal to a specified value.
CONV	Convert a number from one base to another
COS	Return the cosine value of the specified angle.
COT	Return the tangent of the specified angle
CRC32	Calculate the cyclic redundancy check value
DEGREES	Convert radians to degrees
EXP	Return the specified power of e
FLOOR	Return the largest integer less than or equal to a specified value.
GENERATOR	Generate a random number within the specified range
LN	Return the natural logarithm of the specified value.
LOG	Return the logarithm of a specified number
LOG10	Return the base-10 logarithm of the specified value.
LOG2	Return the base-2 logarithm of a specified number
MOD	Return the remainder of dividing two numbers
NEG	Return the negative of a specified number
NORMAL	Generate a random number from a normal distribution
PI	Return the value of pi.
POWER	Return the specified power of the specified number
POW	Return the specified power of the specified number
RADIANS	Convert angle to radian
RAND	Generate a random number between 0 and 1
RANDOM	Generate a random number between 0 and 1
RANDSTR	Generate a random string of the specified length
ROUND	Round a number to the specified number of decimal places
SIGN	Return the sign of the number (-1, 0, or 1).
SIN	Return the sine value of a specified angle.
SQRT	Return the square root of a specified number
TAN	Return the tangent value at a specified angle.
TRUNCATE	Truncates a number to the specified number of decimal places
UNIFORM	Generate uniformly distributed random numbers within a specified range
ZIPF	Generate random numbers following Zipf's distribution

Comparison functions

Function name	Feature description
GREATEST	Return the maximum value in the parameter list.
INTERVAL	Return the index of the largest parameter that is less than the first parameter.
ISNULL	Tests whether an expression is NULL
LEAST	Return the minimum value in the parameter list.

Flow control functions

Function name	Feature description
CASE	Conditional expression, which returns different values based on conditions.
IF	Conditional function, which returns different values based on conditions.
IFNULL	If the first parameter is NULL, return the second parameter.
NULLIF	If the two parameters are equal, NULL is returned.
ORA_DECODE	Oracle DECODE Function

Aggregate functions

Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions ignore null values. They are often used together with the GROUP BY clause in a SELECT statement.

All aggregate functions are deterministic. Whenever they are called with a given set of input values, they return the same value.

In OceanBase Database aggregate functions, only one value expression is allowed. For example, COUNT(c1, c2) is not supported; only COUNT(c1) is supported.

Function name	Feature description
APPROX_COUNT_DISTINCT	Return the number of approximately unique values
ARG_MAX	Parameter for returning the maximum value
ARG_MIN	Parameter for returning the minimum value
AVG	Return the average value of the numbers.
BIT_AND	Perform a bitwise AND operation on numeric values
BIT_OR	Perform a bitwise OR operation on floating-point numbers
BIT_XOR	Perform a bitwise XOR operation on floating-point values
COUNT	Number of rows returned or number of non-NULL values
GROUP_CONCAT	Concatenate multiple row values into a single string
GROUPING	Return the grouping information of the GROUP BY clause.
MAX	Return the maximum value
MIN	Return the minimum value
STD	Return Standard Deviation
STDDEV	Return Standard Deviation
STDDEV_POP	Return the overall standard deviation
STDDEV_SAMP	Return Sample Standard Deviation
SUM	Return the sum of the values.
VAR_POP	Return the overall variance
VAR_SAMP	Return Sample Variance
VARIANCE	Return variance

Analytical functions

Analytical functions (also called window functions in some databases) are similar to aggregate functions in that they always operate on a set of rows. However, whereas an aggregate function returns only one row for each group, an analytical function can return multiple rows per group, with each row being the result of a logical computation within the window. Analytical functions can significantly optimize queries that require self-joins.

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

Function name	Feature description
APPROX_COUNT_DISTINCT	Return the number of approximately unique values
AVG	Return the average value of the numbers.
BIT_AND	Perform a bitwise AND operation on numeric values
BIT_OR	Perform a bitwise OR operation on floating-point numbers
BIT_XOR	Perform a bitwise XOR operation on floating-point values
COUNT	Number of rows returned or number of non-NULL values
CUME_DIST	Return Cumulative Distribution Value
DENSE_RANK	Return Dense Ranking
FIRST_VALUE	Return the first value in the window
LAG	Return the value of the previous row or the first N rows
LAST_VALUE	Return the last value in the window
LEAD	Return the value of the next row or the next N rows
MAX	Return the maximum value
MIN	Return the minimum value
NTH_VALUE	Return the N-th value in the window
NTILE	Split the result set into a specified number of groups.
PERCENT_RANK	Return Percentage Ranking
PERCENTILE_CONT	Return Continuous Percentile Values
RANK	Return Ranking
ROW_NUMBER	Return Line Number
STD	Return Standard Deviation
STDDEV	Return Standard Deviation
STDDEV_POP	Return the overall standard deviation
STDDEV_SAMP	Return Sample Standard Deviation
SUM	Return the sum of the values.
VAR_POP	Return the overall variance
VAR_SAMP	Return Sample Variance
VARIANCE	Return variance
WINDOW_FUNNEL	Calculates the maximum number of consecutive steps that meet the specified conditions within a given time window.

Encryption functions

Encryption functions are used for data encryption and decryption, mainly including encryption and decryption functions.

Function name	Feature description
AES_DECRYPT	Decrypt data using the AES algorithm
AES_ENCRYPT	Encrypt data using the AES algorithm
COMPRESS	Compressed String
DECODE	Decode String
DES_DECRYPT	Decrypt data using the DES algorithm
DES_ENCRYPT	Encrypt data using the DES algorithm
ENCODE	encoded string
ENCRYPT	Encrypted String
ENHANCED_AES_DECRYPT	Decrypt data using the enhanced AES algorithm
ENHANCED_AES_ENCRYPT	Encrypt data using the enhanced AES algorithm
MD5	Calculate MD5 hash
PASSWORD	Calculate password hash value
RANDOM_BYTES	Generate random bytes
SHA	Calculate SHA hash value
SHA1	Calculate SHA1 hash value
SHA2	Calculate SHA2 hash value
SM3	Calculate the SM3 hash value.
SM4_DECRYPT	Decrypt data using the SM4 algorithm
SM4_ENCRYPT	Encrypt data using the SM4 algorithm
UNCOMPRESS	Decompress String
UNCOMPRESSED_LENGTH	Length after Decompression
VALIDATE_PASSWORD_STRENGTH	Verify Password Strength

Information functions

Information functions are used to return specified system information.

Function name	Feature description
BENCHMARK	Execute the expression for the specified number of times and return the execution time
CHARSET	Character set of the returned string
COLLATION	Sort order of the returned string
CONNECTION_ID	Return the ID of the current connection.
COERCIBILITY	Mandatory return string value
CURRENT_ROLE	Return Current Role
CURRENT_USER	Return Current User
DATABASE	Return the current database name.
FOUND_ROWS	Returns the number of rows affected by the previous SELECT statement
ICU_VERSION	Return the version of the ICU library.
LAST_INSERT_ID	Return the ID of the last inserted record
OB_TRANSACTION_ID	Return the current transaction ID.
OB_VERSION	Return OceanBase Database version
ROW_COUNT	Returns the number of rows affected by the previous statement
SCHEMA	Return the current database name.
SESSION_USER	Return the current session user
SYSTEM_USER	Return system user
USER	Return Current User
VERSION	Return Database Version

JSON Functions

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

Function name	Feature description
JSON_ARRAY	Create a JSON array
JSON_ARRAY_APPEND	Append values to a JSON array
JSON_ARRAY_INSERT	Insert a value into a JSON array
JSON_ARRAYAGG	Aggregate multiple rows into a JSON array
JSON_CONTAINS	Check if the JSON document contains a specified value
JSON_CONTAINS_PATH	Check whether the JSON document contains the specified path.
JSON_DEPTH	Depth of returned JSON document
JSON_EXTRACT	Extract values from a JSON document
JSON_INSERT	Insert a value into a JSON document
JSON_KEYS	Return all keys of the JSON object.
JSON_LENGTH	Return the length of a JSON array or object.
JSON_MERGE	Merged JSON document
JSON_MERGE_PATCH	Merging documents using JSON merge patches
JSON_MERGE_PRESERVE	Merged JSON Documents with Duplicated Keys Retained
JSON_OBJECT	Create a JSON object
JSON_OBJECTAGG	Aggregate multiple rows into a JSON object
JSON_OVERLAPS	Check if two JSON documents overlap
JSON_PRETTY	Formatted JSON document
JSON_QUOTE	Convert a string to a JSON string.
JSON_REMOVE	Delete a value from a JSON document
JSON_REPLACE	Replace values in the JSON document
JSON_SCHEMA_VALID	Verify whether a JSON document complies with a schema
JSON_SCHEMA_VALIDATION_REPORT	Return the JSON mode validation report
JSON_SEARCH	Search for a value in a JSON document
JSON_SET	Set the value in the JSON document.
JSON_STORAGE_SIZE	Return the storage size of the JSON document.
JSON_TABLE	Convert JSON data into a table format
JSON_TYPE	Type of the returned JSON value
JSON_UNQUOTE	Remove quotation marks from a JSON string
JSON_VALID	Verify the validity of a JSON document
JSON_VALUE	Extract a scalar value from a JSON document
MEMBER	Check whether a value is a member of a JSON array

XML functions

XML functions are used to process and analyze XML data.

Function name	Feature description
EXTRACTVALUE	Extract values from an XML document
UPDATEXML	Update the value in the XML document.

TABLE FUNCTION

A TABLE FUNCTION returns a result set that can be referenced in the FROM clause.

Name	Feature description
JSON_TABLE	Convert JSON data into a table format
FILES	Read data from an external file and return the table schema

For more information, see Overview of TABLE FUNCTIONS.

Spatial functions

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

Function name	Feature description
_ST_GeoHash	Calculate the GeoHash value of a geometric object
Space Constructor	Create a spatial geometry object
Spatial relationship functions	Test the relationships between spatial objects
Geometric Processing Functions	Process spatial geometric objects
Geometric Format Conversion Functions	Convert the format of geometric objects
Geometric property functions	Get the properties of a geometric object
Spatial format conversion functions	Convert the format of spatial data
Spatial analysis functions	Analyze spatial data
Spatial Operation Functions	Perform operations on spatial objects
Space accessor functions	Access the properties of a spatial object
Create a geometric value function	Create a geometric value

Lock functions

Lock functions are used to manipulate locks.

Function name	Feature description
GET_LOCK	Acquire a named lock
IS_FREE_LOCK	Check if the named lock is available.
IS_USED_LOCK	Check if the named lock is in use
RELEASE_ALL_LOCKS	Release all named locks
RELEASE_LOCK	Release a named lock

Bitmap functions

Bitmap functions are used to process highly compressed bitmap data.

Function name	Feature description
Bitmap Constructor	Create Bitmap Object
Bitmap Operation Functions	Manipulate Bitmap Objects
Bitmap Arithmetic Functions	Perform operations on bitmaps
Bitmap Cardinality Function	Calculate the cardinality of a bitmap
Bitmap predicate function	Determine Bitmap Attributes
Bitmap Output Functions	Output Bitmap Data
Bitmap Aggregate Functions	Aggregated Bitmap Data

Array functions

Array functions are used to process and analyze array data.

Function name	Feature description
Array Constructor	Create an array object
Array Operation Functions	Manipulate Array Objects
Array Output Functions	Output array data
Array judgment function	Determine Array Attributes

Mapping functions

Function name	Feature description
Mapping Constructor	Create a mapping object
Mapping Operation Function	Action Mapping Object
Mapping Judgment Function	Determine the mapped attribute

Audit functions

Function name	Feature description
AUDIT_LOG_FILTER_REMOVE_FILTER	Remove Audit Log Filter
AUDIT_LOG_FILTER_REMOVE_USER	Remove Audit Log User Filter
AUDIT_LOG_FILTER_SET_FILTER	Set Audit Log Filter
AUDIT_LOG_FILTER_SET_USER	Set Audit Log User Filter

Performance mode functions

Function name	Feature description
FORMAT_BYTES	Formatted Bytes
FORMAT_PICO_TIME	Format Picosecond Time

Text processing functions

Function name	Feature description
TOKENIZE	Segment the text.

Other functions

Other functions mainly include those that are not easily categorized.

Function name	Feature description
ANY_VALUE	Return any non-NULL value
BIN_TO_UUID	Convert binary to UUID
COALESCE	Return the first non-NULL value
DEFAULT	Return Column Default Value
INET6_ATON	Convert an IPv6 address to binary.
INET6_NTOA	Convert binary to IPv6 address
INET_ATON	Convert an IPv4 address to an integer.
INET_NTOA	Convert an integer to an IPv4 address.
IS_IPV4	Check whether a string is a valid IPv4 address.
IS_IPV4_COMPAT	Check whether the IPv6 address is IPv4-compatible.
IS_IPV6	Check whether a string is a valid IPv6 address.
IS_UUID	Check whether a string is a valid UUID
MATCH	Full-text search matching functions
NAME_CONST	Create a named constant
NVL	If the first parameter is NULL, return the second parameter.
SLEEP	Pause execution for the specified number of seconds
UUID	Generate UUID
UUID_SHORT	Generate Short UUID
UUID_TO_BIN	Convert UUID to binary
VALUES	Return the values from the INSERT statement
COLLECT_FILE_LIST	List Metadata of Files Matching the Pattern in External Storage Paths (for External Table Mechanisms)
ISNAN	Check if a floating-point value is NaN (requires the ClickHouse extension function mode)
LOAD_FILE	Read an external file as a BLOB by using the LOCATION clause (for the expression and statement, see`LOAD_FILE`(Note)

OceanBase

Customer Stories

Documentation