Functions

This document describes the supported SQL functions and operators.

Generally functions will return null on null input, although note that this is not true in all circumstances, especially for null-aware functions like COALESCE and IFNULL.

Definitions noted with a accept different input arguments.

New functions for this version are annotated with the icon.

Conversion Functions

BOOLEAN any: any → boolean

Cast any to a BOOLEAN, raises an error if cast is not possible. Note BOOLEAN does not require parenthesis, however any aliases do.
Alias for CAST(any AS BOOLEAN)

BLOB (varchar: varchar) → blob

New in 0.14
Cast varchar to blob, raises an error if cast is not possible.

CAST (any: any AS type) → type

Cast any to type, raises an error if cast is not possible.
Also implemented as individual cast functions (BOOLEAN, INTEGER, FLOAT, VARCHAR).

INT (num: numeric) → numeric

Alias for INTEGER

INTEGER (num: numeric) → numeric

Convert num to an integer.
INTEGER is a psuedo-type, CAST is not supported and values may be coerced to NUMERIC.

FLOAT (num: numeric) → numeric

Convert num to a floating point number.
FLOAT is a psuedo-type, CAST is not supported and values may be coerced to NUMERIC.

NUMERIC any: any → numeric

Cast any to a floating point number, raises an error if cast is not possible. Note NUMERIC does not require parenthesis, however any aliases do.
Alias for CAST(any AS NUMERIC)

SAFE_CAST (any: any AS type) → type

Alias for TRY_CAST(any AS type)

STR (any: any) → varchar

Alias of VARCHAR(any) and CAST(any AS VARCHAR)

STRING (any: any) → varchar

Alias of VARCHAR(any) and CAST(any AS VARCHAR)

STRUCT (any: any) → struct

Cast any to a struct / dictionary.
Alias of CAST(any AS STRUCT)

TIMESTAMP iso8601: varchar → timestamp

Cast an ISO 8601 format string to a timestamp, raises an error if cast is not possible. Note TIMESTAMP does not require parenthesis, however any aliases do.
Alias for CAST(iso8601 AS TIMESTAMP)

TRY_CAST (any: any AS type) → type

Cast any to type, if cast is not possible, returns null.

VARCHAR (any) → varchar

Cast any to a string, raises an error if cast is not possible.
Alias for CAST(any AS VARCHAR)

Date & Time Functions

For more details, see Working with Timestamps.

current_date → timestamp

Return the current date, in UTC. Note current_date does not require parenthesis.

current_time → timestamp

Return the current date and time, in UTC. Note current_time does not require parenthesis.

DATE (ts: timestamp) → timestamp

Remove any time information, leaving just the date part of ts.

DATE_FORMAT (ts: timestamp, format: varchar) → varchar

Formats ts as a string using format.

DATEPART(unit: varchar, ts: timestamp) → numeric

Alias of EXTRACT(unit FROM ts)

DATE_TRUNC (unit: varchar, ts: timestamp) → varchar

Returns ts truncated to unit.

DATEDIFF (unit: varchar, start: timestamp, end: timestamp) → numeric

Calculate the difference between the start and end timestamps in a given unit.

DAY (timestamp) → numeric

Extract day number from a timestamp. See EXTRACT.

EXTRACT (unit FROM timestamp) → numeric

Extract unit of a timestamp.
- NANOSECOND
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- TIME
- DATE
- DAY
- DAYOFWEEK / DOW
- WEEK
- ISOWEEK
- MONTH
- QUARTER
- DAYOFYEAR / DOY
- YEAR
- ISOYEAR
- DECADE
- CENTURY
- EPOCH
- JULIAN

FROM_UNIXTIME (timestamp: numeric) → timestamp

Return a timestamp representation of an Unix Timestamp.
Related: UNIXTIME

NOW () → timestamp

Alias for current_time

TIME () → timestamp

Returns the current iime (UTC).

TIME_BUCKET (timestamp, multiple: numeric, unit: varchar) → timestamp

Floor timestamps into fixed time interval buckets. unit is optional and will be day if not provided.

TODAY () → timestamp

Alias for current_date

HOUR (ts: timestamp) → numeric

Returns the hour of the day from ts. The value ranges from 0 to 23.
Alias for EXTRACT(hour FROM ts)

MINUTE (ts: timestamp) → numeric

Returns the minute of the hour from ts. The value ranges from 0 to 59.
Alias for EXTRACT(minute FROM ts)

MONTH (ts: timestamp) → numeric

Returns the month of the year from ts. The value ranges from 1 to 12.
Alias for EXTRACT(month FROM ts)

QUARTER (ts: timestamp) → numeric

Returns the quarter of the year from ts. The value ranges from 1 to 4.
Alias for EXTRACT(quarter FROM ts)

SECOND (ts: timestamp) → numeric

Returns the second of the minute from ts. The value ranges from 0 to 59.
Alias for EXTRACT(second FROM ts)

TIMEDIFF (unit: varchar, start: timestamp) → numeric

Calculate the difference between the start and end times.

UNIXTIME () → numeric

Return the current time as a Unix Timestamp.
Related: FROM_UNIXTIME, current_time

UNIXTIME (timestamp: timestamp) → numeric

Return timestamp in Unix Timestamp representation.
Related: FROM_UNIXTIME

UTC_TIMESTAMP () → timestamp

Alias for current_time

WEEK (ts: timestamp) → numeric

Returns the week of the year from ts. The value ranges from 1 to 53.
Alias for EXTRACT(week FROM ts)

YEAR (ts: timestamp) → numeric

Returns the year from ts.
Alias for EXTRACT(year FROM ts)

Infix Functions

These are functions that are called similar to comparison operators:

numeric + numeric → numeric

Numeric addition

timestamp + interval → timestamp

Timestamp and Interval addition

numeric - numeric → numeric

Numeric subtraction

timestamp - interval → timestamp

Timestamp and Interval subtraction

timestamp - timestamp → interval

Timestamp subtraction

numeric * numeric → numeric

Numeric multiplication

numeric / numeric → numeric

Numeric division

numeric DIV numeric → integer

Integer division

numeric % numeric → numeric

Numeric modulo (remainder)

varchar || varchar → varchar

String concatenation

inet | inet → bool

IP address containment

integer | integer → integer

Bitwise OR

integer & integer → integer

Bitwise AND

integer ^ integer → integer

Bitwise XOR

List Functions

For more details, see Working with Lists.

array: list[index: numeric] → value

Return the indexth element from array.

GET (array: list, index: numeric) → value

Alias of array[index]

GREATEST (array: list) → value

Return the greatest value in array.
Related: LEAST

LEAST (array: list) → value

Return the smallest value in array.
Related: GREATEST

LEN (array: list) → numeric

Alias of LENGTH(array)

LENGTH (array: list) → numeric

Returns the number of elements in array.

LIST_CONTAINS (array: list, value) → boolean

Return true if array contains value.
See also SEARCH(array, value)

LIST_CONTAINS_ANY (array: list, values: list) → boolean

Return true if array contains any elements in values.

LIST_CONTAINS_ALL (array: list, values: list) → boolean

Return true if array contains all of elements in values.

SEARCH (array: list, value) → boolean

Return true if array contains value.

SORT (array: list) → list

Return array in ascending order.

Numeric Functions

ABS (x: numeric) → numeric

Alias of ABSOLUTE

ABSOLUTE (x: numeric) → numeric

Returns the absolute value of x.

CEIL (x: numeric) → numeric

Alias of CEILING

CEILING (x: numeric) → numeric

Returns x rounded up to the nearest integer.
Related: FLOOR

E () → numeric

Returns the constant e, also known as Euler's number.
Related: LN.

FLOOR (x: numeric) → numeric

Returns x rounded down to the nearest integer.

PHI () → numeric

Returns the constant φ (phi), also known as the golden ratio.

PI () → numeric

Returns the constant π (pi).

POWER (base: numeric, exponent: numeric**) → _numeric

Returns base to the power of exponent.

LN (x: numeric) → numeric

Returns the natural logarithm of x.
Related: E, LOG, LOG10, LOG2

LOG (x: numeric, base: numeric) → numeric

Returns the logarithm of x for base base.
Related: LN, LOG10, LOG2

LOG10 (x: numeric) → numeric

Returns the logarithm for base 10 of x.
Related: LN, LOG, LOG2

LOG2 (x: numeric) → numeric

Returns the logarithm for base 2 of x.
Related: LN, LOG, LOG10

ROUND (x: numeric) → numeric

Returns x rounded to the nearest integer.

ROUND (x: numeric, places: numeric) → numeric

Returns x rounded to places decimal places.

SIGN (x: numeric) → numeric

Returns the signum function of x; 0 if x is 0, -1 if x is less than 0 and 1 if x is greater than 0.

SIGNUM (x: numeric) → numeric

Alias for SIGN

SQRT (x: numeric) → numeric

Returns the square root of x.

TRUNC (x: numeric) → numeric

Alias of TRUNCATE

TRUNCATE (x: numeric) → numeric

Returns x rounded to integer by dropping digits after decimal point.

String Functions

Functions for examining and manipulating string values.

str: varchar[index: numeric] → varchar

Subscript operator, return the indexth character from str.

CONCAT (list: array<varchar>) → varchar

Returns the result of concatenating, or joining, of two or more string values in an end-to-end manner.
Related: CONCAT_WS

CONCAT_WS (separator: varchar, list: array<varchar>) → varchar

Returns the result of concatenating, or joining, of two or more string values with a separator used to delimit individual values.
Related: CONCAT

ENDS_WITH (str: varchar, value: varchar) → boolean

Return true if str ends with value.
Related: STARTS_WITH

GET (str: varchar, index: numeric) → varchar

Alias of str[index]

LEFT (str: varchar, n: numeric) → varchar

Extract the left-most n characters of str.
Related: RIGHT

LEN (str: varchar) → numeric

Alias of LENGTH

LENGTH (str: varchar) → numeric

Returns the length of str in characters.

LEVENSHTEIN (str1: varchar, str2: varchar) → numeric

Returns the Levenshtein Distance between str1 and str2

LOWER (str: varchar) → varchar

Converts str to lowercase.
Related: UPPER, TITLE

LTRIM (str: varchar) → varchar

Remove leading whitespace from str.
Related: RTRIM, TRIM

POSITION (substring: varchar IN string: varchar) → numeric

Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.

REVERSE (str: varchar) → varchar

Returns str with the characters in reverse order.

RIGHT (str: varchar, n: numeric) → varchar

Extract the right-most n characters of str.
Related: LEFT

RTRIM (str: varchar) → varchar

Remove trailing whitespace from str.
Related: LTRIM, TRIM

SOUNDEX (str: varchar) → varchar

Returns a character string containing the phonetic representation of char. See Soundex 🡕.

SEARCH (str: varchar, substring: varchar) → boolean

Return true if str contains substring.

SPLIT (str: varchar) → array

Splits str on commas (,) and returns an array.

SPLIT (str: varchar, delimiter: varchar) → array

Splits str on delimiter and returns an array.

SPLIT (str: varchar, delimiter: varchar, limit: integer) → array

Splits str on delimiter and returns an array of size at most limit. The last element in the array contains the remaining part of the string. limit must be greater than zero.

SUBSTRING (str: varchar, start: numeric) → varchar

Return substring from a string from start position to the end of str.

SUBSTRING (str: varchar, start: numeric, length: numeric) → varchar

Return substring from a string from start position for length characters.

STARTS_WITH (str: varchar, value: varchar) → boolean

Return true if str starts with value.
Related: ENDS_WITH

TITLE (str: varchar) → varchar

Returns str with the first letter of each work in upper case.
Related: LOWER, UPPER

TRIM ( [ LEADING | TRAILING | BOTH ] [ chars: varchar FROM ] str: varchar ) → varchar

Removes leading and trailing chars from str, if chars is not specified, whitespace is removed. Note that any instance of a character in chars is removed in any order they appear.
The LEADING modifier removes chars from the start of str.
The TRAILING modifier removes chars from the end of str.
The BOTH modifier removes chars from both the start and end of str, this is the default behaviour if no positional modifier is supplied.
Related: LTRIM, RTRIM

UPPER (str: varchar) → varchar

Converts str to uppercase.
Related: LOWER, TITLE

Struct Functions

For more details, see Working with Structs.

struct -> key → value

New in 0.14
Return the value for key from object.

struct ->> key → varchar

New in 0.14
Return the value for key from object, non NULL values are cast to VARCHAR. Related: -> operator

object: struct[key: varchar] → value

Subscript operator Alias of object->key

GET (object: struct, key: varchar) → value

Alias of object->key

SEARCH (object: struct, value: varchar) → boolean

Return true if any of the values in object is value. Note SEARCH does not match struct keys.

System Functions

VERSION () → varchar

Return the version of the query engine.

Other Functions

BASE64_DECODE (str: varchar) → varchar

Decode BASE64 encoded value, str. Related: BASE64_ENCODE

BASE64_ENCODE (str: varchar) → varchar

Encode str with BASE64 encoding.
Related: BASE64_DECODE

BASE85_DECODE (str: varchar) → varchar

Decode BASE85 encoded value, str. Related: BASE85_ENCODE

BASE85_ENCODE (str: varchar) → varchar

Encode str with BASE85 encoding.
Related: BASE85_DECODE

COALESCE (arg1, arg2, ...) → value

Return the first item from args which is not null.
Related: IFNULL

COSINE_SIMILARITY (str: varchar, value: varchar) → double

New in 0.15
Perform an ad hoc cosine similarity comparison between str and value

GENERATE_SERIES (start: numeric, stop: numeric) → list<numeric>

Return a numeric list between start and stop, with a step of 1.

GENERATE_SERIES (start: numeric, stop: numeric, step: numeric) → list<numeric>

Return a numeric list between start and stop, with an increment of step.

GENERATE_SERIES (start: timestamp, stop: timestamp, interval) → list<timestamp>

Return a timestamp list between start and stop, with a interval of step.

HASH (value: any) → varchar

Calculate the CityHash (64 bit) of value.

HEX_DECODE (str: varchar) → varchar

Decode hexidecimal (BASE16) encoded value, str.
Related: HEX_ENCODE

HEX_ENCODE (str: varchar) → varchar

Encode str with hexadecimal (BASE16) encoding.
Related: HEX_DECODE

IIF (condition, true_value, false_value) → input type

Return the true_value if the condition evaluates to True, otherwise return the false_value.

IFNULL (check_expression: any, replacement_value: any) → input type

Returns check_expression if not null, otherwise returns replacement_value. Related: COALESCE

NORMAL () → numeric

Random number from a normal (Gaussian) distribution; distribution is centred at 0.0 and has a standard deviation of 1.0. Per record.

NULLIF (value1: any, value2: any) → input type

Returns null if value1 equals value2, otherwise returns value1.

MD5 (str: varchar) → varchar

Calculate the MD5 hash of str.

RAND () → numeric

Returns a random number between 0 and 1. Per record.

RANDOM () → numeric

Alias of RAND

RANDOM_STRING (length: numeric) → varchar

Returns a random string of lowercase alphabetic characters with a length of length. Per record.

SHA1 (str: varchar) → varchar

Calculate the SHA1 hash of str.
Related: SHA224, SHA256, SHA384, SHA512

SHA224 (str: varchar) → varchar

Calculate the SHA224 hash of str.
Related: SHA1, SHA256, SHA384, SHA512

SHA256 (str: varchar) → varchar

Calculate the SHA256 hash of str.
Related: SHA1, SHA224, SHA384, SHA512

SHA384 (str: varchar) → varchar

Calculate the SHA384 hash of stre.
Related: SHA1, SHA224, SHA256, SHA512

SHA512 (str: varchar) → varchar

Calculate the SHA512 hash of str.
Related: SHA1, SHA224, SHA256, SHA384

UNNEST (array: list) → relation

Create a virtual relation with a row for each element in array.