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)
CAST
(any: any AS type) → type
Cast any to type, raises an error if cast is not possible.
Also implemented as individual cast functions.
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
** New in 0.11**
Integer division
numeric %
numeric → numeric
Numeric modulo (remainder)
varchar ||
varchar → varchar
String concatenation
inet |
inet → bool
IP address containment
integer |
integer → integer
** New in 0.12**
Bitwise OR
integer &
integer → integer
** New in 0.12**
Bitwise AND
integer ^
integer → integer
** New in 0.12**
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.
object: struct[
key: varchar]
→ value
Subscript operator, return the value for key from object.
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
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.