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.

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:

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