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

Temporal Functions Aggregate Functions

On this page

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
now
nth_value
ntile
nullif
percent_rank
pi
pow
power
quarter
radians
rand
random
rank
regexp_count
regexp_extract
regexp_extract_all
regexp_like
regexp_position
regexp_replace
regexp_split
repeat
replace
reverse
round
row_number
rpad
rtrim
second
sha256
sha512
sign
sin
sinh
split
split_part
sqrt
starts_with
stddev
stddev_pop
stddev_samp
strpos
substr
substring
sum
tan
tanh
to_hex
to_timestamp_seconds
to_unixtime
translate
trim
truncate
upper
uuid
year

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`