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. These include 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 Specified Number of Days to a Date |
| ADDTIME | Add a specified 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 a date. |
| DAYOFMONTH | Return the number of days in a date |
| DAYOFWEEK | Return the day of the week for a date (1=Sunday). |
| DAYOFYEAR | Number of Days in a Year for the Return 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 in which a specified date falls. |
| 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 the 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 date and time 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 | Week of the return date |
| WEEKDAY | Return the day of the week for a date (0=Monday). |
| WEEKOFYEAR | Week of the year |
| YEAR | Extract year from date |
| YEARWEEK | Return the year and week of a date |
Note
When the tenant is configured with sql_func_extension_mode='ClickHouse', the DATE_ADD/DATE_SUB functions in the preceding table use the ClickHouse-compatible implementation (including truncation of intervals, subsecond precision, and restrictions on combining DATE with subsecond units). For more information, see DATE_ADD / DATE_SUB (ClickHouse mode).
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 position. |
| editDistance | Calculate the edit distance between two strings (byte-level, requires a ClickHouse extended 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 | Formats 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 an IP address to an integer. |
| KEYVALUE | Extract a value from a 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 string set based on bit values |
| MAX_PT | Return 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) |
| 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 | Returns 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 string of spaces of the specified length. |
| 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 | Returns an angle in radians whose cosine is the specified NUMBER expression, also known as the arccosine. |
| ASIN | Returns 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 a 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 a specified number. |
| LOG | Return the logarithm of a specified number |
| LOG10 | Return the base-10 logarithm of a specified value. |
| LOG2 | Return the logarithm of a specified number to base 2. |
| MOD | Returns 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 a given number |
| POW | Return the specified power of a given 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 from the Zipf 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 two parameters are equal, NULL is returned. |
| ORA_DECODE | Oracle-Compatible 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 the numbers |
| BIT_OR | Perform a bitwise OR operation on floating-point numbers |
| BIT_XOR | Perform a bitwise XOR operation on the numbers |
| COUNT | Number of rows returned or number of non-NULL values |
| GROUP_CONCAT | Concatenates multiple-row values into a single string |
| GROUPING | Return the grouping information from 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
Analysis functions (also known as 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 a single row for each group, an analysis function can return multiple rows per group, with each row being the result of a window-based calculation. Analysis 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 the numbers |
| BIT_OR | Perform a bitwise OR operation on floating-point numbers |
| BIT_XOR | Perform a bitwise XOR operation on the numbers |
| 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 |
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 | Encoding 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 |
| SHA2 | Calculate SHA2 hash value |
| SM3 | Calculate 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 | Sorting rule for returned strings |
| CONNECTION_ID | Return the ID of the current connection. |
| COERCIBILITY | Return the mandatory value of the string. |
| CURRENT_ROLE | Return Current Role |
| CURRENT_USER | Return Current User |
| DATABASE | Return the current database name. |
| FOUND_ROWS | Return the number of rows affected by the previous SELECT statement |
| ICU_VERSION | Return the ICU library version. |
| LAST_INSERT_ID | Return the last inserted ID |
| 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 to System User |
| USER | Return Current User |
| VERSION | Return Database Version |
JSON Functions
JSON functions are used to create, manipulate, and search for 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 a JSON object |
| JSON_LENGTH | Return the length of a JSON array or object. |
| JSON_MERGE | Merged JSON document |
| JSON_MERGE_PATCH | Use JSON merge patches to merge documents |
| JSON_MERGE_PRESERVE | Merged JSON Documents with Duplicated Keys Preserved |
| JSON_OBJECT | Create a JSON object |
| JSON_OBJECTAGG | Aggregate multiple rows into a JSON object |
| JSON_OVERLAPS | Check whether two JSON documents overlap |
| JSON_PRETTY | Format JSON documents |
| 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 the JSON document complies with the schema |
| JSON_SCHEMA_VALIDATION_REPORT | Return the JSON mode validation report |
| JSON_SEARCH | Search for a value in a JSON document |
| JSON_SET | Set a 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 double quotes 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 the value is a member of the 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 together 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 a geometric object |
| Geometric property functions | Get the properties of a geometric object. |
| Space 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 function | 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 Operation Functions | Perform operations on the bitmap |
| Bitmap cardinality function | Calculate the cardinality of a bitmap |
| Bitmap predicate function | Determine Bitmap Attributes |
| Bitmap Output Function | Output Bitmap Data |
| Bitmap aggregate functions | Aggregate 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 Function | Output Array Data |
| Array Check Function | Determine Array Attributes |
Mapping functions
Function name |
Feature description |
|---|---|
| Mapping Constructor | Create a mapping object |
| Mapping Operation Functions | 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 classified.
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 | Default value of the return column |
| 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 | Lists the file metadata that matches the specified pattern in external storage paths (used by external table mechanisms). |
| ISNAN | Check whether 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) |