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, seeLOAD_FILE(Note) |