Functions

The following functions are supported by Opteryx. Click a name for details.

Array Functions

• ARRAY_CONTAINS — Test if array contains item.
• ARRAY_CONTAINS_ALL — Test if array contains all items from set.
• ARRAY_CONTAINS_ANY — Test if array contains any item from set.
• GREATEST — Return maximum element of an array.
• LEAST — Return minimum element of an array.
• SORT — Sort an array.

Conversion Functions

• HUMANIZE — Format number in human-readable form.

Date & Time Functions

• CURRENT_DATE — Current date (SQL-92).
• CURRENT_TIME — Current time (SQL-92).
• CURRENT_TIMESTAMP — Current timestamp (SQL-92).
• DATEDIFF — Difference between two dates in the specified unit.
• DATE_FORMAT — Format date/timestamp as string.
• EXTRACT — Extract a part from a date/timestamp.
• FROM_UNIXTIME — Convert Unix timestamp to TIMESTAMP.
• TIMEDIFF — Difference between two times.
• TIME_BUCKET — Bucket date into fixed-width intervals.
• UNIXTIME — Convert TIMESTAMP to Unix epoch seconds.

Hash & Encoding Functions

• BASE64_DECODE — Base64 decode.
• BASE64_ENCODE — Base64 encode.
• BASE85_DECODE — Base85 decode.
• BASE85_ENCODE — Base85 encode.
• HASH — Generic hash.
• HEX_DECODE — Hex decode.
• HEX_ENCODE — Hex encode.
• MD5 — MD5 hash.
• SHA1 — SHA-1 hash.
• SHA224 — SHA-224 hash.
• SHA256 — SHA-256 hash.
• SHA384 — SHA-384 hash.
• SHA512 — SHA-512 hash.

Numeric Functions

• ABS — Absolute value.
• CEILING — Round up to nearest integer.
• E — Euler's number e.
• FLOOR — Round down to nearest integer.
• LOG — Logarithm with arbitrary base.
• PHI — Golden ratio φ.
• PI — Mathematical constant π.
• POWER — Raise base to exponent (SQL-92).
• ROUND — Round to nearest integer.
• SIGN — Sign of number (-1, 0, 1).
• SQRT — Square root.
• TRUNC — Truncate a numeric or temporal value.

String Functions

• ASCII — Return ASCII codepoint of first character.
• CHAR — Convert codepoint to character.
• CONCAT — Concatenate strings.
• CONCAT_WS — Concatenate with separator.
• INITCAP — Capitalise first letter of each word.
• LEFT — Return leftmost N characters.
• LENGTH — Return length of string.
• LEVENSHTEIN — Levenshtein edit distance between two strings.
• LOWER — Convert string to lowercase.
• LPAD — Left-pad string to width.
• LTRIM — Trim leading characters.
• MATCH — Full-text match.
• POSITION — Find position of substring.
• REGEXP_REPLACE — Replace regex matches.
• REPLACE — Replace occurrences of substring.
• REVERSE — Reverse a string.
• RIGHT — Return rightmost N characters.
• RPAD — Right-pad string to width.
• RTRIM — Trim trailing characters.
• SOUNDEX — Return Soundex phonetic code.
• SPLIT — Split string into array.
• SUBSTRING — Extract substring.
• TRIM — Trim leading and trailing characters.
• UPPER — Convert string to uppercase.

Struct/JSON Functions

• JSONB_OBJECT_KEYS — Extract keys from JSON object.

Utility Functions

• COALESCE — Return first non-null argument.
• CONNECTION_ID — Current connection identifier.
• DATABASE — Current database name.
• IFNOTNULL — Return second argument if first is not null.
• IFNULL — Return value if not null, else default.
• IIF — Inline if: return second or third arg based on condition.
• NORMAL — Generate normally-distributed random numbers.
• NULLIF — Return null if equal, else first value.
• RANDOM — Generate random numbers.
• RANDOM_STRING — Generate random strings.
• USER — Current user name.
• UTC_TIMESTAMP — Current UTC timestamp.
• VERSION — Database version string.

Vector / Embedding Functions

• COSINE_DISTANCE — Cosine distance between two vectors.
• COSINE_SIMILARITY — Cosine similarity between two vectors.
• EMBED — Convert text to an embedding vector.