All Supported Functions

`abs`

Returns the absolute value of x. Examples:

examples.sql

select abs(-1) as value; -- value '1.0'

Supported Signatures

function abs(tinyint) returns tinyint
function abs(smallint) returns smallint
function abs(bigint) returns bigint
function abs(double) returns double
function abs(decimal(p, s)) returns decimal(p, s)
function abs(real) returns real

🔗 Official Documentation

`acos`

Returns the arc cosine of x. Examples:

examples.sql

select acos(0.5) as value; -- value '1.0471975511965976'

Supported Signatures

function acos(double) returns double

🔗 Official Documentation

`array_agg`

Returns an array created from the input x elements. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9);  SELECT array_agg(column1) AS value FROM tbl;
 -- value '[1, 5, 9]'

Supported Signatures

function array_agg($1) returns array<$1>

🔗 Official Documentation

`array_distinct`

Remove duplicate values from the array x. Examples:

examples.sql

SELECT array_distinct([1, 2, 3]) as value; -- value '[1, 2, 3]'
SELECT array_distinct([1, 1, 2, 3]) as value; -- value '[1, 2, 3]'

Supported Signatures

function array_distinct(array<$3>) returns array<$3>

🔗 Official Documentation

`array_except`

Returns an array of elements in x but not in y, without duplicates. Examples:

examples.sql

SELECT array_except([1, 1, 2, 3], [1]) AS value; -- value '[2, 3]'

Supported Signatures

function array_except(array<$3>, array<$3>) returns array<$3>

🔗 Official Documentation

`array_intersect`

Returns an array of the elements in the intersection of x and y, without duplicates. Examples:

examples.sql

SELECT array_intersect([1, 1, 2, 3], [1]) AS value; -- value '[1]'
SELECT array_intersect(['hello', 'world'], ['hello']) AS value; -- value '[hello]'

Supported Signatures

function array_intersect(array<$3>, array<$3>) returns array<$3>

🔗 Official Documentation

`array_join`

Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result. Examples:

examples.sql

SELECT array_join(['hello', 'world'], ' beautiful ') AS value; -- value 'hello beautiful world'

Supported Signatures

function array_join(array<$1>, varchar) returns varchar
function array_join(array<$1>, varchar, varchar) returns varchar

🔗 Official Documentation

`array_max`

Returns the maximum value of input array. Supported Signatures

function array_max(array<$1>) returns $1

🔗 Official Documentation

`array_min`

Returns the minimum value of input array. Supported Signatures

function array_min(array<$1>) returns $1

🔗 Official Documentation

`array_position`

Returns the position of the first occurrence of the element in array x (or 0 if not found). Examples:

examples.sql

SELECT array_position([1, 1, 2], 1) AS value; -- value '1'

Supported Signatures

function array_position(array<$1>, $1) returns bigint

🔗 Official Documentation

`array_remove`

Remove all elements that equal element from array x. Examples:

examples.sql

SELECT array_remove([1, 1, 2], 1) AS value; -- value '[2]'
SELECT array_remove(['hello', 'bad', 'world'], 'bad') AS value; -- value '[hello, world]'

Supported Signatures

function array_remove(array<$3>, $3) returns array<$3>

🔗 Official Documentation

`array_sort`

Sorts and returns the array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array. Supported Signatures

function array_sort(array<$3>) returns array<$3>

🔗 Official Documentation

`array_union`

Returns an array of the elements in the union of x and y, without duplicates. Examples:

examples.sql

SELECT array_union([1, 1], [2]) as value; -- value '[1, 2]'

Supported Signatures

function array_union(array<$3>, array<$3>) returns array<$3>

🔗 Official Documentation

`asin`

Returns the arc sine of x. Examples:

examples.sql

select asin(1) as value; -- value '1.5707963267948966'

Supported Signatures

function asin(double) returns double

🔗 Official Documentation

`atan`

Returns the arc tangent of x. Examples:

examples.sql

select atan(0) as value; -- value '0.0'

Supported Signatures

function atan(double) returns double

🔗 Official Documentation

`atan2`

Returns the arc tangent of y / x. Examples:

examples.sql

select atan2(1, 0.5) as value; -- value '1.1071487177940904'

Supported Signatures

function atan2(double, double) returns double

🔗 Official Documentation

`avg`

Returns the average (arithmetic mean) of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT avg(tbl.column1) AS value FROM tbl; -- value '5.0'

Supported Signatures

function avg(double) returns double
function avg(decimal(p, s)) returns decimal(p, s)
function avg(real) returns real

🔗 Official Documentation

`cardinality`

Returns the cardinality (size) of the array x. Examples:

examples.sql

SELECT cardinality([1, 1, 2]) as value; -- value '3'

Supported Signatures

function cardinality(array<$3>) returns bigint

🔗 Official Documentation

`cbrt`

Returns the cube root of x. Examples:

examples.sql

SELECT cbrt(328509) as value; -- value '69.0'

Supported Signatures

function cbrt(double) returns double

🔗 Official Documentation

`ceil`

This is an alias for ceiling(). Examples:

examples.sql

SELECT ceil(1.3) AS value; -- value '2.0'

Supported Signatures

function ceil(bigint) returns bigint
function ceil(double) returns double
function ceil(real) returns real

🔗 Official Documentation

`ceiling`

Returns x rounded up to the nearest integer. Supported Signatures

function ceiling(bigint) returns bigint
function ceiling(double) returns double
function ceiling(real) returns real

🔗 Official Documentation

`chr`

Returns the Unicode code point n as a single character string. Examples:

examples.sql

select chr(61) as value; -- value '='

Supported Signatures

function chr(bigint) returns varchar

🔗 Official Documentation

`coalesce`

Returns the first non-null value in the argument list. Like a CASE expression, arguments are only evaluated if necessary. Examples:

examples.sql

CREATE TABLE tbl as VALUES ('1', null), ('2', '2'), (null, '3');
SELECT 
    COALESCE(column1, column2) as value
FROM tbl;
 -- value '('1', '2', '3')'

Supported Signatures

function coalesce($1, ...) returns $1

🔗 Official Documentation

`codepoint`

Returns the Unicode code point of the only character of string. Examples:

examples.sql

SELECT codepoint('😊') AS value; -- value '128522'

Supported Signatures

function codepoint(varchar) returns bigint

🔗 Official Documentation

`concat`

Returns the concatenation of string1, string2, …, stringN. This function provides the same functionality as the SQL-standard concatenation operator (||). Examples:

examples.sql

select concat('hello ', 'world!') as value; -- value 'hello world!'

Supported Signatures

function concat($3, array<$3>) returns array<$3>
function concat(array<$3>, ...) returns array<$3>
function concat(array<$3>, $3) returns array<$3>
function concat(varchar, varchar) returns varchar
function concat(varchar, ...) returns varchar

🔗 Official Documentation

`concat_ws`

Using the first array string0 element as separator, returns the concatenation of all subsequent strings string1, string2, … If `string0“ is null, then the return value is null. Any null values provided in the arguments after the separator are skipped. Examples:

examples.sql

SELECT concat_ws('😊', 'hey', 'there', 'people') AS value; -- value 'hey😊there😊people'

Supported Signatures

function concat_ws(varchar, array<varchar>) returns varchar
function concat_ws(varchar, ...) returns varchar

🔗 Official Documentation

`contains`

Takes an array and an element. Returns true if the array contains the element, false if not. Examples:

examples.sql

SELECT contains(['hello', 'world'], 'hello') AS value; -- value 'true'
SELECT contains(['hello', 'world'], '😊') AS value; -- value 'false'

Supported Signatures

function contains(array<$1>, $1) returns boolean

🔗 Official Documentation

`cos`

Returns the cosine of x. Examples:

examples.sql

select cos(1.0) as value; -- value '0.5403023058681398'

Supported Signatures

function cos(double) returns double

🔗 Official Documentation

`cosh`

Returns the hyperbolic cosine of the given value. Examples:

examples.sql

SELECT cosh(0) AS value; -- value '1.0'

Supported Signatures

function cosh(double) returns double

🔗 Official Documentation

`count`

Returns the number of input rows. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT count(tbl.column1) AS value FROM tbl; -- value '3'

Supported Signatures

function count() returns bigint
function count($1) returns bigint

🔗 Official Documentation

`cume_dist`

Returns the cumulative distribution of a value in a group of values. The result is the number of rows preceding or peer with the row in the window ordering of the window partition divided by the total number of rows in the window partition. Thus, any tie values in the ordering will evaluate to the same distribution value. Supported Signatures

function cume_dist() returns double

🔗 Official Documentation

`current_date`

Returns the current date as of the start of the query. Examples:

examples.sql

SELECT current_date() AS value; -- value '2024-06-20'

Supported Signatures

function current_date() returns date

Note: current_date() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`current_time`

Returns the current time with time zone as of the start of the query. Examples:

examples.sql

SELECT current_time() AS value; -- value '13:52:34.245364'

Supported Signatures

function current_time() returns time

Note: current_time() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`current_timestamp`

Returns the current timestamp with time zone as of the start of the query, with 3 digits of subsecond precision, Examples:

examples.sql

SELECT current_timestamp() AS value; -- value '2024-06-21T13:53:41.809016Z'

Supported Signatures

function current_timestamp() returns timestamp
function current_timestamp(0) returns timestamp(0)
function current_timestamp(3) returns timestamp(3)
function current_timestamp(6) returns timestamp(6)
function current_timestamp(9) returns timestamp(9)

Note: current_timestamp() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`date`

This is an alias for CAST(x AS date). Supported Signatures

function date(varchar) returns date

🔗 Official Documentation

`date_diff`

Takes a unit, timestamp1, and timestamp2. Returns timestamp2 - timestamp1 expressed in terms of unit. Examples:

examples.sql

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00') AS value; -- value '86400'
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC') AS value; -- value '24'
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02') AS value; -- value '1'
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789') AS value; -- value '86400'
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789') AS value; -- value '86400123'

Supported Signatures

function date_diff(varchar, date, date) returns bigint
function date_diff(varchar, time(p), time(p)) returns bigint
function date_diff(varchar, timestamp(p), timestamp(p)) returns bigint

🔗 Official Documentation

`date_format`

Formats timestamp as a string using format. Examples:

examples.sql

SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H') AS value; -- value '10-20-2022 05'

Supported Signatures

function date_format(timestamp(p), varchar) returns varchar

🔗 Official Documentation

`date_parse`

Parses string into a timestamp using format. Supported Signatures

function date_parse(varchar, varchar) returns timestamp

🔗 Official Documentation

`date_part`

Supported Signatures

function date_part(varchar, time(p)) returns double
function date_part(varchar, timestamp(p)) returns double
function date_part(varchar, date) returns double

🔗 Official Documentation

`date_trunc`

Returns x truncated to unit. Supported Signatures

function date_trunc(varchar, timestamp(p)) returns timestamp(p)

🔗 Official Documentation

`day`

Returns the day of the month from x. Examples:

examples.sql

SELECT day(date('2022-10-20')) AS value; -- value '20'

Supported Signatures

function day(date) returns bigint
function day(timestamp(p)) returns bigint

🔗 Official Documentation

`day_of_month`

This is an alias for day(). Supported Signatures

function day_of_month(date) returns bigint
function day_of_month(timestamp(p)) returns bigint

🔗 Official Documentation

`day_of_week`

Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday). Supported Signatures

function day_of_week(date) returns bigint
function day_of_week(timestamp(p)) returns bigint

🔗 Official Documentation

`day_of_year`

Returns the day of the year from x. The value ranges from 1 to 366. Supported Signatures

function day_of_year(date) returns bigint
function day_of_year(timestamp(p)) returns bigint

🔗 Official Documentation

`degrees`

Converts angle x in radians to degrees. Examples:

examples.sql

SELECT degrees(3.14159) AS value; -- value '179.9998547447946'

Supported Signatures

function degrees(double) returns double

🔗 Official Documentation

`dense_rank`

Returns the rank of a value in a group of values. This is similar to rank(), except that tie values do not produce gaps in the sequence. Examples:

examples.sql

SELECT name, DENSE_RANK() OVER (ORDER BY score) AS dense_rank_col FROM (
    VALUES ('A', 10), ('B', 20), ('C', 20), ('D', 30)
) AS t (name, score)
 -- value '('A', 1), ('B', 2), ('C', 2), ('D', 3)'

Supported Signatures

function dense_rank() returns bigint

🔗 Official Documentation

`dow`

This is an alias for day_of_week(). Supported Signatures

function dow(date) returns bigint
function dow(timestamp(p)) returns bigint

🔗 Official Documentation

`doy`

This is an alias for day_of_year(). Supported Signatures

function doy(date) returns bigint
function doy(timestamp(p)) returns bigint

🔗 Official Documentation

`element_at`

Returns element of array at given index. If index > 0, this function provides the same functionality as the SQL-standard subscript operator ([]), except that the function returns NULL when accessing an index larger than array length, whereas the subscript operator would fail in such a case. If index < 0, element_at accesses elements from the last to the first. Supported Signatures

function element_at(array<$3>, bigint) returns $3

🔗 Official Documentation

`exp`

Returns Euler’s number raised to the power of x. Examples:

examples.sql

select exp(1.0) as value; -- value '2.718281828459045'

Supported Signatures

function exp(double) returns double

🔗 Official Documentation

`first_value`

Returns the first value of the window. Supported Signatures

function first_value($1) returns $1

🔗 Official Documentation

`flatten`

Flattens an array(array(T)) to an array(T) by concatenating the contained arrays. Examples:

examples.sql

SELECT flatten(
    ARRAY [ARRAY [1, 2],
    ARRAY [3, 4]])
AS value;
 -- value '[1, 2, 3, 4]'

Supported Signatures

function flatten(array<array<$3>>) returns array<$3>

🔗 Official Documentation

`floor`

Returns x rounded down to the nearest integer. Supported Signatures

function floor(bigint) returns bigint
function floor(double) returns double

🔗 Official Documentation

`from_unixtime`

Returns the UNIX timestamp unixtime as a timestamp with time zone. unixtime is the number of seconds since 1970-01-01 00.00.00 UTC. Examples:

examples.sql

SELECT from_unixtime(999919900) AS value; -- value '2001-09-08T03:31:40'

Supported Signatures

function from_unixtime(bigint) returns timestamp

🔗 Official Documentation

`greatest`

Returns the largest of the provided values. Examples:

examples.sql

SELECT greatest(1, 2, 3) AS value; -- value '3'

Supported Signatures

function greatest($3, ...) returns $3

🔗 Official Documentation

`grouping`

Supported Signatures

function grouping(...) returns int

🔗 Official Documentation

`hour`

Returns the hour of the day from x. The value ranges from 0 to 23. Supported Signatures

function hour(time(p)) returns bigint
function hour(timestamp(p)) returns bigint

🔗 Official Documentation

`if`

Evaluates expression parameter and returns second if condition is true, otherwise null is returned and true_value is not evaluated. Examples:

examples.sql

SELECT IF(2 > 1, 'HIGHER', 'LOWER') AS value; -- value 'HIGHER'

Supported Signatures

function if(boolean, $1, $1) returns $1

🔗 Official Documentation

`index`

Alias for strpos() function. Examples:

examples.sql

SELECT INDEX('abcdefg', 'c') -- value '3'

Supported Signatures

function index(varchar, varchar) returns bigint

🔗 Official Documentation

`is_nan`

Determine if x is not-a-number. Examples:

examples.sql

select is_nan(1.1) as value; -- value 'false'

Supported Signatures

function is_nan(double) returns boolean
function is_nan(real) returns boolean

🔗 Official Documentation

`lag`

Returns the value at offset rows before the current row in the window partition. Offsets start at 0, which is the current row. The offset can be any scalar expression. The default offset is 1. If the offset is null, an error is raised. If the offset refers to a row that is not within the partition, the default_value is returned, or if it is not specified null is returned. The lag() function requires that the window ordering be specified. Window frame must not be specified. Examples:

examples.sql

SELECT name, score, LAG(score) OVER (PARTITION BY name) AS lead_score FROM (
    VALUES ('A', 10), ('A', 20), ('D', 20), ('D', 30)
) AS t (name, score)
 -- value '('A', 10, ), ('A', 20, 10), ('D', 20, ), ('D', 30, 20)'

Supported Signatures

function lag($1) returns $1

🔗 Official Documentation

`last_value`

Returns the last value of the window. Supported Signatures

function last_value($1) returns $1

🔗 Official Documentation

`lead`

Returns the value at offset rows after the current row in the window partition. Offsets start at 0, which is the current row. The offset can be any scalar expression. The default offset is 1. If the offset is null, an error is raised. If the offset refers to a row that is not within the partition, the default_value is returned, or if it is not specified null is returned. The lead() function requires that the window ordering be specified. Window frame must not be specified. Examples:

examples.sql

SELECT name, score, LEAD(score) OVER (PARTITION BY name) AS lead_score FROM (
    VALUES ('A', 10), ('A', 20), ('D', 20), ('D', 30)
) AS t (name, score)
 -- value '('A', 10, 20), ('A', 20, ), ('D', 20, 30), ('D', 30, )'

Supported Signatures

function lead($1) returns $1

🔗 Official Documentation

`least`

Returns the smallest of the provided values. Examples:

examples.sql

SELECT LEAST(5,6,7,1,2,3,4) -- list of columns or values -- value '1'

Supported Signatures

function least($3, ...) returns $3

🔗 Official Documentation

`length`

Returns the length of string in characters. Examples:

examples.sql

select length('Hello World!') as value; -- value '12'

Supported Signatures

function length(varchar) returns bigint

🔗 Official Documentation

`levenshtein_distance`

Returns the Levenshtein edit distance of string1 and string2, i.e. the minimum number of single-character edits (insertions, deletions or substitutions) needed to change string1 into string2. Supported Signatures

function levenshtein_distance(varchar, varchar) returns bigint

🔗 Official Documentation

`ln`

Returns the natural logarithm of x. Examples:

examples.sql

select ln(2.1) as value; -- value '0.7419373447293773'

Supported Signatures

function ln(double) returns double

🔗 Official Documentation

`localtimestamp`

Returns the current timestamp as of the start of the query, with 3 digits of subsecond precision. Supported Signatures

function localtimestamp(0) returns timestamp(0)
function localtimestamp(3) returns timestamp(3)
function localtimestamp(6) returns timestamp(6)
function localtimestamp(9) returns timestamp(9)

Note: localtimestamp() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`log`

Returns the base b logarithm of x. Examples:

examples.sql

SELECT log(2.19, 1) AS value; -- value '0.0'

Supported Signatures

function log(double, double) returns double

🔗 Official Documentation

`log10`

Returns the base 10 logarithm of x. Examples:

examples.sql

select log10(10) as value; -- value '1.0'

Supported Signatures

function log10(double) returns double

🔗 Official Documentation

`log2`

Returns the base 2 logarithm of x. Examples:

examples.sql

select log2(2) as value; -- value '1.0'

Supported Signatures

function log2(double) returns double

🔗 Official Documentation

`lower`

Converts string to lowercase. Examples:

examples.sql

select lower('Hello World!') as value; -- value 'hello world!'

Supported Signatures

function lower(varchar) returns varchar

🔗 Official Documentation

`lpad`

Left pads string to size characters with padstring. If size is less than the length of string, the result is truncated to size characters. size must not be negative and padstring must be non-empty. Examples:

examples.sql

select lpad('😉', 5, '*') as value; -- value '****😉'

Supported Signatures

function lpad(varchar, bigint, varchar) returns varchar

🔗 Official Documentation

`ltrim`

Removes leading whitespace from string. Examples:

examples.sql

select ltrim('   😉') as value; -- value '😉'

Supported Signatures

function ltrim(varchar) returns varchar

🔗 Official Documentation

`max`

Returns the maximum value of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT max(tbl.column1) AS value FROM tbl; -- value '9'

Supported Signatures

function max($1, ...) returns $1

🔗 Official Documentation

`millisecond`

Returns the millisecond of the second from x. Supported Signatures

function millisecond(time(p)) returns bigint
function millisecond(timestamp(p)) returns bigint

🔗 Official Documentation

`min`

Returns the minimum value of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT min(tbl.column1) AS value FROM tbl; -- value '1'

Supported Signatures

function min($1, ...) returns $1

🔗 Official Documentation

`minute`

Returns the minute of the hour from x. Supported Signatures

function minute(time(p)) returns bigint
function minute(timestamp(p)) returns bigint

🔗 Official Documentation

`mod`

Returns the modulus (remainder) of n divided by m. Supported Signatures

function mod(bigint, bigint) returns bigint
function mod(double, double) returns double

🔗 Official Documentation

`month`

Returns the month of the year from x. Examples:

examples.sql

SELECT month(cast ('2024-11-01' AS date )) AS value; -- value '11'

Supported Signatures

function month(date) returns bigint
function month(timestamp(p)) returns bigint

🔗 Official Documentation

`now`

This is an alias for current_timestamp. Examples:

examples.sql

select now() as value; -- value '2023-02-28 00:17:47.154040'

Supported Signatures

function now() returns timestamp

Note: now() is stable, i.e. might return a different value for a different query execution. 🔗 Official Documentation

`nth_value`

Returns the value at the specified offset from the beginning of the window. Offsets start at 1. The offset can be any scalar expression. If the offset is null or greater than the number of values in the window, null is returned. It is an error for the offset to be zero or negative. Supported Signatures

function nth_value($1, bigint) returns $1

🔗 Official Documentation

`ntile`

Divides the rows for each window partition into n buckets ranging from 1 to at most n. Bucket values will differ by at most 1. If the number of rows in the partition does not divide evenly into the number of buckets, then the remainder values are distributed one per bucket, starting with the first bucket. Examples:

examples.sql

SELECT name, NTILE(5) OVER (ORDER BY score) AS ntile_col FROM (
    VALUES ('A', 10), ('A', 20), ('A', 30), ('A', 40)
) AS t (name, score)
 -- value '('A', 1), ('A', 2), ('D', 3), ('D', 4)'

Supported Signatures

function ntile(bigint) returns bigint

🔗 Official Documentation

`nullif`

Returns null if value1 equals value2, otherwise returns value1. Examples:

examples.sql

SELECT nullif(1, 1) AS value; -- value 'NULL'
SELECT nullif(2, 1) AS value; -- value '2'

Supported Signatures

function nullif($1, $1) returns $1

🔗 Official Documentation

`percent_rank`

Returns the percentage ranking of a value in group of values. The result is (r - 1) / (n - 1) where r is the rank() of the row and n is the total number of rows in the window partition. Examples:

examples.sql

SELECT name, PERCENT_RANK() OVER (ORDER BY score) AS percent_rank_col FROM (
    VALUES ('A', 10), ('B', 20), ('C', 20), ('D', 30)
) AS t (name, score)
 -- value '('A', 0.0), ('B', 0.33333333), ('C', 0.33333333), ('D', 1.0)'

Supported Signatures

function percent_rank() returns double

🔗 Official Documentation

`pi`

Returns the constant Pi. Examples:

examples.sql

SELECT PI() -- value '3.141592653589793'

Supported Signatures

function pi() returns double

🔗 Official Documentation

`pow`

This is an alias for power(). Supported Signatures

function pow(double, double) returns double

🔗 Official Documentation

`power`

Returns x raised to the power of p. Examples:

examples.sql

select power(2, 8) as value; -- value '256'

Supported Signatures

function power(double, double) returns double

🔗 Official Documentation

`quarter`

Returns the quarter of the year from x. The value ranges from 1 to 4. Supported Signatures

function quarter(date) returns bigint
function quarter(timestamp(p)) returns bigint

🔗 Official Documentation

`radians`

Converts angle x in degrees to radians. Examples:

examples.sql

SELECT radians(360) AS value; -- value '6.283185307179586'

Supported Signatures

function radians(double) returns double

🔗 Official Documentation

`rand`

This is an alias for random(). Supported Signatures

function rand() returns double

Note: rand() is volatile, i.e. might return a different value for the same input. 🔗 Official Documentation

`random`

Returns a pseudo-random value in the range 0.0 <= x < 1.0. Examples:

examples.sql

select random() as value; -- value '0.8781138533805801'

Supported Signatures

function random() returns double

Note: random() is volatile, i.e. might return a different value for the same input. 🔗 Official Documentation

`rank`

Returns the rank of a value in a group of values. The rank is one plus the number of rows preceding the row that are not peer with the row. Thus, tie values in the ordering will produce gaps in the sequence. The ranking is performed for each window partition. Examples:

examples.sql

SELECT name, RANK() OVER (ORDER BY score) AS rank_col FROM (
    VALUES ('A', 10), ('B', 20), ('C', 20), ('D', 30)
) AS t (name, score)
 -- value '('A', 1), ('B', 2), ('C', 2), ('D', 4)'

Supported Signatures

function rank() returns bigint

🔗 Official Documentation

`regexp_count`

Returns the number of occurrence of pattern in string. Supported Signatures

function regexp_count(varchar, joniregexp) returns bigint

🔗 Official Documentation

`regexp_extract`

Returns the first substring matched in a string by the regular expression pattern. Examples:

examples.sql

SELECT REGEXP_EXTRACT('email_address@gmail.com', '@(.*?)\.' -- value 'gmail'

Supported Signatures

function regexp_extract(varchar, joniregexp) returns varchar
function regexp_extract(varchar, joniregexp, bigint) returns varchar

🔗 Official Documentation

`regexp_extract_all`

Returns the substrings of a string matched by the regular expression pattern. Supported Signatures

function regexp_extract_all(varchar, joniregexp) returns array<varchar>
function regexp_extract_all(varchar, joniregexp, bigint) returns array<varchar>

🔗 Official Documentation

`regexp_like`

Evaluates the regular expression pattern and determines if it is contained within string. Examples:

examples.sql

SELECT REGEXP_like('email_address@gmail.com', '.*@.*\..*') AS is_valid_email -- value 'TRUE'

Supported Signatures

function regexp_like(varchar, joniregexp) returns boolean

🔗 Official Documentation

`regexp_position`

Returns the index of the first occurrence (counting from 1) of pattern in string. Returns -1 if not found. Supported Signatures

function regexp_position(varchar, joniregexp) returns bigint
function regexp_position(varchar, joniregexp, bigint) returns bigint
function regexp_position(varchar, joniregexp, bigint, bigint) returns bigint

🔗 Official Documentation

`regexp_replace`

Removes every instance of the substring matched by the regular expression pattern from string. Examples:

examples.sql

SELECT REGEXP_REPLACE('text foo another text', 'foo', 'bar') -- value 'text bar another text'

Supported Signatures

function regexp_replace(varchar, joniregexp) returns varchar
function regexp_replace(varchar, joniregexp, varchar) returns varchar

🔗 Official Documentation

`regexp_split`

Splits string using the regular expression pattern and returns an array. Trailing empty strings are preserved. Supported Signatures

function regexp_split(varchar, joniregexp) returns array<varchar>

🔗 Official Documentation

`repeat`

Repeat element for count times. Examples:

examples.sql

select repeat('😉', 4) as value; -- value '😉😉😉😉'

Supported Signatures

function repeat($1, bigint) returns array<$1>

🔗 Official Documentation

`replace`

Removes all instances of search from string. Examples:

examples.sql

select replace('Hello There', 'There') as value; -- value 'Hello '

Supported Signatures

function replace(varchar, varchar, varchar) returns varchar

🔗 Official Documentation

`reverse`

Returns string with the characters in reverse order. Examples:

examples.sql

select reverse('Hello There') as value; -- value 'ereht olleH'

Supported Signatures

function reverse(array<$3>) returns array<$3>
function reverse(varchar) returns varchar

🔗 Official Documentation

`round`

Returns x rounded to the nearest integer. Examples:

examples.sql

select round(2.1) as value; -- value '2.0'

Supported Signatures

function round(double) returns double
function round(double, bigint) returns double
function round(real) returns real
function round(real, bigint) returns real

🔗 Official Documentation

`row_number`

Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition. Examples:

examples.sql

SELECT name, ROW_NUMBER() OVER (ORDER BY score) AS row_number_col FROM (
    VALUES ('A', 10), ('B', 20), ('C', 20), ('D', 30)
) AS t (name, score)
 -- value '('A', 1), ('B', 2), ('C', 3), ('D', 4)'

Supported Signatures

function row_number() returns bigint

🔗 Official Documentation

`rpad`

Right pads string to size characters with padstring. If size is less than the length of string, the result is truncated to size characters. size must not be negative and padstring must be non-empty. Examples:

examples.sql

select rpad('😉', 5, '*') as value; -- value '😉****'

Supported Signatures

function rpad(varchar, bigint, varchar) returns varchar

🔗 Official Documentation

`rtrim`

Removes trailing whitespace from string. Examples:

examples.sql

select rtrim('😉   ') as value; -- value '😉'

Supported Signatures

function rtrim(varchar) returns varchar

🔗 Official Documentation

`second`

Returns the second of the minute from x. Supported Signatures

function second(time(p)) returns bigint
function second(timestamp(p)) returns bigint

🔗 Official Documentation

`sha256`

Computes the SHA256 hash of binary. Examples:

examples.sql

select sha256('Hello World!') as value; -- value '7f83b1657ff1fc53b92dc18148a1d65dfc2d4b1fa3d677284addd200126d9069'

Supported Signatures

function sha256(varbinary) returns varbinary

🔗 Official Documentation

`sha512`

Computes the SHA512 hash of binary. Examples:

examples.sql

select sha512('Hello World!') as value; -- value '861844d6704e8573fec34d967e20bcfef3d424cf48be04e6dc08f2bd58c729743371015ead891cc3cf1c9d34b49264b510751b1ff9e537937bc46b5d6ff4ecc8'

Supported Signatures

function sha512(varbinary) returns varbinary

🔗 Official Documentation

`sign`

Returns the signum function of x, that is. Supported Signatures

function sign(decimal(p, s)) returns decimal(1, 0)
function sign(double) returns double
function sign(real) returns real

🔗 Official Documentation

`sin`

Returns the sine of x. Examples:

examples.sql

select sin(2.1) as value; -- value '-0.8632093666488737'

Supported Signatures

function sin(double) returns double

🔗 Official Documentation

`sinh`

Returns the hyperbolic sine of x. Examples:

examples.sql

SELECT sinh(1) AS value; -- value '1.1752011936438014'

Supported Signatures

function sinh(double) returns double

🔗 Official Documentation

`split`

Splits string on delimiter and returns an array. Examples:

examples.sql

SELECT split('hello+world', '+') as value; -- value '[hello, world]'

Supported Signatures

function split(varchar, varchar) returns array<varchar>
function split(varchar, varchar, bigint) returns array<varchar>

🔗 Official Documentation

`split_part`

Splits string on delimiter and returns the field index. Field indexes start with 1. If the index is larger than the number of fields, then null is returned. Examples:

examples.sql

SELECT split_part('hello world', 'world', 1) as value; -- value 'hello'

Supported Signatures

function split_part(varchar, varchar, bigint) returns varchar

🔗 Official Documentation

`sqrt`

Returns the square root of x. Examples:

examples.sql

select sqrt(16) as value; -- value '4.0'

Supported Signatures

function sqrt(double) returns double

🔗 Official Documentation

`starts_with`

Tests whether substring is a prefix of string. Examples:

examples.sql

SELECT starts_with('hello world', 'world') -- value 'false'
SELECT starts_with('hello world', 'hello') ; -- value 'true'

Supported Signatures

function starts_with(varchar, varchar) returns boolean

🔗 Official Documentation

`stddev`

This is an alias for stddev_samp(). Supported Signatures

function stddev(bigint) returns double
function stddev(double) returns double

🔗 Official Documentation

`stddev_pop`

Returns the population standard deviation of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9);  SELECT stddev_pop(column1) as value from tbl;
 -- value '3.265986323710904'

Supported Signatures

function stddev_pop(bigint) returns double
function stddev_pop(double) returns double

🔗 Official Documentation

`stddev_samp`

Returns the sample standard deviation of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9);  SELECT stddev_samp(column1) as value from tbl;
 -- value '4.0'

Supported Signatures

function stddev_samp(bigint) returns double
function stddev_samp(double) returns double

🔗 Official Documentation

`strpos`

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

examples.sql

select strpos('Hello World', 'World') as value; -- value '7'

Supported Signatures

function strpos(varchar, varchar) returns bigint

🔗 Official Documentation

`substr`

This is an alias for substring(). Examples:

examples.sql

select substr('Hello World', 6) as value; -- value ' World'

Supported Signatures

function substr(varchar, bigint) returns varchar
function substr(varchar, bigint, bigint) returns varchar

🔗 Official Documentation

`substring`

Returns the rest of string from the starting position start. Positions start with 1. A negative starting position is interpreted as being relative to the end of the string. Examples:

examples.sql

select substring('Hello World', 6) as value; -- value ' World'

Supported Signatures

function substring(varchar, bigint) returns varchar
function substring(varchar, bigint, bigint) returns varchar

🔗 Official Documentation

`sum`

Returns the sum of all input values. Examples:

examples.sql

CREATE TABLE tbl AS VALUES (1), (5), (9); 
 SELECT sum(tbl.column1) AS value FROM tbl; -- value '15'

Supported Signatures

function sum(bigint) returns bigint
function sum(double) returns double
function sum(decimal(p, s)) returns decimal(38, s)
function sum(real) returns real

🔗 Official Documentation

`tan`

Returns the tangent of x. Examples:

examples.sql

select tan(0.5) as value; -- value '0.5463024898437905'

Supported Signatures

function tan(double) returns double

🔗 Official Documentation

`tanh`

Returns the hyperbolic tangent of x. Examples:

examples.sql

SELECT tanh(1.0) AS value; -- value '0.7615941559557649'

Supported Signatures

function tanh(double) returns double

🔗 Official Documentation

`to_hex`

Encodes binary into a hex string representation. Examples:

examples.sql

SELECT to_hex(cast(1000 as varbinary)) AS value; -- value 'e8030000'

Supported Signatures

function to_hex(varbinary) returns varchar

🔗 Official Documentation

`to_timestamp_seconds`

Supported Signatures

function to_timestamp_seconds($1) returns timestamp

🔗 Official Documentation

`to_unixtime`

Returns timestamp as a UNIX timestamp. Examples:

examples.sql

SELECT to_unixtime(now()) AS value; -- value '1718974807'

Supported Signatures

function to_unixtime(timestamp(p)) returns double

🔗 Official Documentation

`translate`

Returns the source string translated by replacing characters found in the from string with the corresponding characters in the to string. If the from string contains duplicates, only the first is used. If the source character does not exist in the from string, the source character will be copied without translation. If the index of the matching character in the from string is beyond the length of the to string, the source character will be omitted from the resulting string. Examples:

examples.sql

select translate('Hello World', 'Hell', 'N') as value; -- value 'No Word'

Supported Signatures

function translate(varchar, varchar, varchar) returns varchar

🔗 Official Documentation

`trim`

Removes any leading and/or trailing characters as specified up to and including string from source. Examples:

examples.sql

select trim('  😉  ') as value; -- value '😉'

Supported Signatures

function trim(varchar) returns varchar

🔗 Official Documentation

`truncate`

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

examples.sql

SELECT truncate(1.23456, 2) as value; -- value '1.23'

Supported Signatures

function truncate(decimal(p, s), bigint) returns decimal(p, s)
function truncate(decimal(p, s)) returns decimal(rp, 0)
function truncate(double) returns double
function truncate(real) returns real

🔗 Official Documentation

`upper`

Converts string to uppercase. Examples:

examples.sql

select upper('Hello World!') as value; -- value 'HELLO WORLD!'

Supported Signatures

function upper(varchar) returns varchar

🔗 Official Documentation

`uuid`

Returns a pseudo randomly generated UUID (type 4). Examples:

examples.sql

SELECT uuid() as value; -- value '22552909-5560-4234-a383-5a968a4e2a91'

Supported Signatures

function uuid() returns uuid

Note: uuid() is volatile, i.e. might return a different value for the same input. 🔗 Official Documentation

`year`

Returns the year from x. Examples:

examples.sql

SELECT year(cast('2024-01-01' as date)) as value; -- value '2024'

Supported Signatures

function year(date) returns bigint
function year(timestamp(p)) returns bigint

🔗 Official Documentation

Reference

SQL Functions

​abs

​acos

​array_agg

​array_distinct

​array_except

​array_intersect

​array_join

​array_max

​array_min

​array_position

​array_remove

​array_sort

​array_union

​asin

​atan

​atan2

​avg

​cardinality

​cbrt

​ceil

​ceiling

​chr

​coalesce

​codepoint

​concat

​concat_ws

​contains

​cos

​cosh

​count

​cume_dist

​current_date

​current_time

​current_timestamp

​date

​date_diff

​date_format

​date_parse

​date_part

​date_trunc

​day

​day_of_month

​day_of_week

​day_of_year

​degrees

​dense_rank

​dow

​doy

​element_at

​exp

​first_value

​flatten

​floor

​from_unixtime

​greatest

​grouping

​hour

​if

​index

​is_nan

​lag

​last_value

​lead

​least

​length

​levenshtein_distance

​ln

​localtimestamp

​log

​log10

​log2

​lower

​lpad

​ltrim

​max

​millisecond

​min

​minute

`abs`

`acos`

`array_agg`

`array_distinct`

`array_except`

`array_intersect`

`array_join`

`array_max`

`array_min`

`array_position`

`array_remove`

`array_sort`

`array_union`

`asin`

`atan`

`atan2`

`avg`

`cardinality`

`cbrt`

`ceil`

`ceiling`

`chr`

`coalesce`

`codepoint`

`concat`

`concat_ws`

`contains`

`cos`

`cosh`

`count`

`cume_dist`

`current_date`

`current_time`

`current_timestamp`

`date`

`date_diff`

`date_format`

`date_parse`

`date_part`

`date_trunc`

`day`

`day_of_month`

`day_of_week`

`day_of_year`

`degrees`

`dense_rank`

`dow`

`doy`

`element_at`

`exp`

`first_value`

`flatten`

`floor`

`from_unixtime`

`greatest`

`grouping`

`hour`

`if`

`index`

`is_nan`

`lag`

`last_value`

`lead`

`least`

`length`

`levenshtein_distance`

`ln`

`localtimestamp`

`log`

`log10`

`log2`

`lower`

`lpad`

`ltrim`

`max`

`millisecond`

`min`

`minute`

`mod`

`month`