# Functions


This page lists commonly used MatrixOne built-in functions by category. Names follow the canonical forms recognized by the engine. Some functions have aliases, such as `CEIL`/`CEILING` and `LCASE`/`LOWER`. This page is a **summary table** and does not describe every function's parameters.

For operators, `CAST`, and `CASE`/`IF`, see [Operators](operators.md).

## Aggregate

| Function | Purpose |
| --- | --- |
| `ANY_VALUE()` | Returns any value from a group |
| `AVG()` | Arithmetic mean |
| `BIT_AND()` / `BIT_OR()` / `BIT_XOR()` | Bitwise aggregation |
| `BITMAP` family | Bitmap-based deduplication and statistics |
| `COUNT()` | Row count |
| `GROUP_CONCAT()` | Concatenates strings within a group |
| `MAX()` / `MIN()` | Maximum / minimum |
| `MEDIAN()` | Median |
| `STDDEV_POP()` | Population standard deviation |
| `SUM()` | Sum |
| `VARIANCE()` / `VAR_POP()` | Population variance |

## Date and time

| Function | Purpose |
| --- | --- |
| `CURDATE()` / `NOW()` / `SYSDATE()` / `CURRENT_TIMESTAMP()` / `UTC_TIMESTAMP()` | Current date or time |
| `DATE()` / `TIME()` / `TIMESTAMP()` | Extracts or normalizes date-time values |
| `DATE_ADD()` / `DATE_SUB()` | Adds or subtracts time intervals |
| `DATEDIFF()` / `TIMEDIFF()` / `TIMESTAMPDIFF()` | Date or time difference |
| `DATE_FORMAT()` / `STR_TO_DATE()` / `TO_DATE()` | Formatting and parsing |
| `EXTRACT()` | Extracts components such as year, month, or day |
| `YEAR()` / `MONTH()` / `DAY()` / `HOUR()` / `MINUTE()` / `SECOND()` | Component values |
| `DAYOFYEAR()` / `WEEK()` / `WEEKDAY()` | Day of year, week, and weekday index |
| `FROM_UNIXTIME()` / `UNIX_TIMESTAMP()` | Converts to or from a Unix timestamp |
| `TO_DAYS()` / `TO_SECONDS()` | Days or seconds relative to an epoch |
| `CONVERT_TZ()` | Time-zone conversion |

## Mathematical

| Function | Purpose |
| --- | --- |
| `ABS()` | Absolute value |
| `CEIL()` / `CEILING()` / `FLOOR()` / `ROUND()` | Rounding |
| `EXP()` / `LN()` / `LOG()` / `LOG2()` / `LOG10()` / `POWER()` | Exponents and logarithms |
| `PI()` | Pi |
| `RAND()` | Random number in (0,1) |
| `SIN()` / `COS()` / `TAN()` / `COT()` / `ASIN` family / `ATAN()` / `SINH()`, and others | Trigonometric and hyperbolic functions |

Actual support depends on the instance. The table covers the common set found in the documentation.

## String

| Function | Purpose |
| --- | --- |
| `CONCAT()` / `CONCAT_WS()` | Concatenation |
| `LENGTH()` / `CHAR_LENGTH()` / `BIT_LENGTH()` | Length in bytes, characters, or bits |
| `LEFT()` / `SUBSTRING()` / `SUBSTRING_INDEX()` / `SPLIT_PART()` | Extraction and delimiter-based splitting |
| `LOWER()` / `LCASE()` / `UPPER()` / `UCASE()` | Case conversion |
| `LTRIM()` / `RTRIM()` / `TRIM()` / `LPAD()` / `RPAD()` / `SPACE()` / `REPEAT()` | Whitespace and padding |
| `LOCATE()` / `INSTR()` / `FIND_IN_SET()` / `FIELD()` | Position search |
| `STARTSWITH()` / `ENDSWITH()` / `EMPTY()` | Prefix, suffix, and empty-string tests |
| `REVERSE()` | Reverses a string |
| `HEX()` / `UNHEX()` / `BIN()` / `OCT()` | Base and hexadecimal conversion |
| `TO_BASE64()` / `FROM_BASE64()` | Base64 conversion |
| `MD5()` / `SHA1()`/`SHA()` / `SHA2()` | Digests |
| `FORMAT()` | Numeric formatting such as thousands separators |

### Regular expressions

| Function | Purpose |
| --- | --- |
| `REGEXP_LIKE()` / `NOT REGEXP` | Tests whether a value matches |
| `REGEXP_INSTR()` | Starting position of a match |
| `REGEXP_SUBSTR()` | Extracts a matching substring |
| `REGEXP_REPLACE()` | Replaces matching content |

## Vector

Used with the [VECTOR type](data-types.md):

| Function / capability | Purpose |
| --- | --- |
| Vector arithmetic `+ - * /` | Element-wise operations |
| `INNER_PRODUCT()` | Inner product |
| `L2_DISTANCE()` / `COSINE_DISTANCE()` / `COSINE_SIMILARITY()` | Distance and similarity |
| `L1_NORM()` / `L2_NORM()` / `NORMALIZE_L2()` | Norms and normalization |
| `VECTOR_DIMS()` | Dimensions |
| `SUBVECTOR()` | Subvector |
| `CLUSTER_CENTERS()` | Cluster centers |
| `ABS` / `SQRT` / `SUMMATION` / `CAST` on vectors, and others | Element-wise or aggregate helpers |

## JSON

| Function | Purpose |
| --- | --- |
| `JSON_EXTRACT()` | Extracts a value by path |
| `JSON_EXTRACT_STRING()` / `JSON_EXTRACT_FLOAT64()` | Extracts as a string / floating-point value |
| `JSON_QUOTE()` / `JSON_UNQUOTE()` | Adds / removes quotes |
| `JSON_ROW()` | Converts a row to a JSON array |
| `JQ()` / `TRY_JQ()` | Transforms with a jq expression; the latter tolerates errors |

## Table functions

| Function | Purpose |
| --- | --- |
| `GENERATE_SERIES()` | Generates a series of rows |
| `UNNEST()` | Expands a JSON array into a table |

## Window

| Function | Purpose |
| --- | --- |
| `ROW_NUMBER()` | Consecutive row number |
| `RANK()` | Rank with gaps after ties |
| `DENSE_RANK()` | Rank without gaps after ties |

## System and session

| Function | Purpose |
| --- | --- |
| `CURRENT_USER()` / `CURRENT_USER_NAME()` | Current user |
| `CURRENT_ROLE()` / `CURRENT_ROLE_NAME()` | Current role |
| `VERSION()` | Version information, if exposed |
| `PURGE_LOG()` | Purges log data from system tables; requires elevated privileges |

## Other

| Function | Purpose |
| --- | --- |
| `UUID()` | Generates a UUID |
| `SLEEP()` | Pauses for the specified number of seconds |
| `SAMPLE()` | Samples data to reduce the scan range |
| `LOAD_FILE()` / `SAVE_FILE()` | Reads or writes the file content referenced by a DATALINK |
| `STAGE_LIST()` | Lists directories and files in a Stage |
| `SERIAL_EXTRACT()` | Extracts an element from a sequence or tuple |
| `LAST_INSERT_ID()` / `LAST_QUERY_ID()` | Most recent insert ID / query ID |