All Supported Functions
abs
Returns the absolute value of x.
Examples:
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
acos
Returns the arc cosine of x.
Examples:
select acos(0.5) as value; -- value '1.0471975511965976'
Supported Signatures
function acos(double) returns double
array_agg
Returns an array created from the input x elements.
Examples:
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>
array_distinct
Remove duplicate values from the array x.
Examples:
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>
array_except
Returns an array of elements in x but not in y, without duplicates.
Examples:
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>
array_intersect
Returns an array of the elements in the intersection of x and y, without duplicates.
Examples:
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>
array_join
Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result.
Examples:
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
array_max
Returns the maximum value of input array.
Supported Signatures
function array_max(array<$1>) returns $1
array_min
Returns the minimum value of input array.
Supported Signatures
function array_min(array<$1>) returns $1
array_position
Returns the position of the first occurrence of the element in array x (or 0 if not found).
Examples:
SELECT array_position([1, 1, 2], 1) AS value; -- value '1'
Supported Signatures
function array_position(array<$1>, $1) returns bigint
array_remove
Remove all elements that equal element from array x.
Examples:
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>
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>
array_union
Returns an array of the elements in the union of x and y, without duplicates.
Examples:
SELECT array_union([1, 1], [2]) as value; -- value '[1, 2]'
Supported Signatures
function array_union(array<$3>, array<$3>) returns array<$3>
asin
Returns the arc sine of x.
Examples:
select asin(1) as value; -- value '1.5707963267948966'
Supported Signatures
function asin(double) returns double
atan
Returns the arc tangent of x.
Examples:
select atan(0) as value; -- value '0.0'
Supported Signatures
function atan(double) returns double
atan2
Returns the arc tangent of y / x.
Examples:
select atan2(1, 0.5) as value; -- value '1.1071487177940904'
Supported Signatures
function atan2(double, double) returns double
avg
Returns the average (arithmetic mean) of all input values.
Examples:
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
cardinality
Returns the cardinality (size) of the array x.
Examples:
SELECT cardinality([1, 1, 2]) as value; -- value '3'
Supported Signatures
function cardinality(array<$3>) returns bigint
cbrt
Returns the cube root of x.
Examples:
SELECT cbrt(328509) as value; -- value '69.0'
Supported Signatures
function cbrt(double) returns double
ceil
This is an alias for ceiling().
Examples:
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
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
chr
Returns the Unicode code point n as a single character string.
Examples:
select chr(61) as value; -- value '='
Supported Signatures
function chr(bigint) returns varchar
coalesce
Returns the first non-null value in the argument list. Like a CASE expression, arguments are only evaluated if necessary.
Examples:
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
codepoint
Returns the Unicode code point of the only character of string.
Examples:
SELECT codepoint('😊') AS value; -- value '128522'
Supported Signatures
function codepoint(varchar) returns bigint
concat
Returns the concatenation of string1, string2, …, stringN. This function provides the same functionality as the SQL-standard concatenation operator (||).
Examples:
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
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:
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
contains
Takes an array and an element. Returns true if the array contains the element, false if not.
Examples:
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
cos
Returns the cosine of x.
Examples:
select cos(1.0) as value; -- value '0.5403023058681398'
Supported Signatures
function cos(double) returns double
cosh
Returns the hyperbolic cosine of the given value.
Examples:
SELECT cosh(0) AS value; -- value '1.0'
Supported Signatures
function cosh(double) returns double
count
Returns the number of input rows.
Examples:
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
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
current_date
Returns the current date as of the start of the query.
Examples:
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.
current_time
Returns the current time with time zone as of the start of the query.
Examples:
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.
current_timestamp
Returns the current timestamp with time zone as of the start of the query, with 3 digits of subsecond precision,
Examples:
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.
date
This is an alias for CAST(x AS date).
Supported Signatures
function date(varchar) returns date
date_diff
Takes a unit, timestamp1, and timestamp2. Returns timestamp2 - timestamp1 expressed in terms of unit.
Examples:
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
date_format
Formats timestamp as a string using format.
Examples:
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
date_parse
Parses string into a timestamp using format.
Supported Signatures
function date_parse(varchar, varchar) returns timestamp
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
date_trunc
Returns x truncated to unit.
Supported Signatures
function date_trunc(varchar, timestamp(p)) returns timestamp(p)
day
Returns the day of the month from x.
Examples:
SELECT day(date('2022-10-20')) AS value; -- value '20'
Supported Signatures
function day(date) returns bigint
function day(timestamp(p)) returns bigint
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
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
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
degrees
Converts angle x in radians to degrees.
Examples:
SELECT degrees(3.14159) AS value; -- value '179.9998547447946'
Supported Signatures
function degrees(double) returns double
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:
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
dow
This is an alias for day_of_week().
Supported Signatures
function dow(date) returns bigint
function dow(timestamp(p)) returns bigint
doy
This is an alias for day_of_year().
Supported Signatures
function doy(date) returns bigint
function doy(timestamp(p)) returns bigint
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
exp
Returns Euler’s number raised to the power of x.
Examples:
select exp(1.0) as value; -- value '2.718281828459045'
Supported Signatures
function exp(double) returns double
first_value
Returns the first value of the window.
Supported Signatures
function first_value($1) returns $1
flatten
Flattens an array(array(T)) to an array(T) by concatenating the contained arrays.
Examples:
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>
floor
Returns x rounded down to the nearest integer.
Supported Signatures
function floor(bigint) returns bigint
function floor(double) returns double
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:
SELECT from_unixtime(999919900) AS value; -- value '2001-09-08T03:31:40'
Supported Signatures
function from_unixtime(bigint) returns timestamp
greatest
Returns the largest of the provided values.
Examples:
SELECT greatest(1, 2, 3) AS value; -- value '3'
Supported Signatures
function greatest($3, ...) returns $3
grouping
Supported Signatures
function grouping(...) returns int
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
if
Evaluates expression parameter and returns second if condition is true, otherwise null is returned and true_value is not evaluated.
Examples:
SELECT IF(2 > 1, 'HIGHER', 'LOWER') AS value; -- value 'HIGHER'
Supported Signatures
function if(boolean, $1, $1) returns $1
index
Alias for strpos() function.
Examples:
SELECT INDEX('abcdefg', 'c') -- value '3'
Supported Signatures
function index(varchar, varchar) returns bigint
is_nan
Determine if x is not-a-number.
Examples:
select is_nan(1.1) as value; -- value 'false'
Supported Signatures
function is_nan(double) returns boolean
function is_nan(real) returns boolean
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:
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
last_value
Returns the last value of the window.
Supported Signatures
function last_value($1) returns $1
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:
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
least
Returns the smallest of the provided values.
Examples:
SELECT LEAST(5,6,7,1,2,3,4) -- list of columns or values -- value '1'
Supported Signatures
function least($3, ...) returns $3
length
Returns the length of string in characters.
Examples:
select length('Hello World!') as value; -- value '12'
Supported Signatures
function length(varchar) returns bigint
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
ln
Returns the natural logarithm of x.
Examples:
select ln(2.1) as value; -- value '0.7419373447293773'
Supported Signatures
function ln(double) returns double
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.
log
Returns the base b logarithm of x.
Examples:
SELECT log(2.19, 1) AS value; -- value '0.0'
Supported Signatures
function log(double, double) returns double
log10
Returns the base 10 logarithm of x.
Examples:
select log10(10) as value; -- value '1.0'
Supported Signatures
function log10(double) returns double
log2
Returns the base 2 logarithm of x.
Examples:
select log2(2) as value; -- value '1.0'
Supported Signatures
function log2(double) returns double
lower
Converts string to lowercase.
Examples:
select lower('Hello World!') as value; -- value 'hello world!'
Supported Signatures
function lower(varchar) returns varchar
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:
select lpad('😉', 5, '*') as value; -- value '****😉'
Supported Signatures
function lpad(varchar, bigint, varchar) returns varchar
ltrim
Removes leading whitespace from string.
Examples:
select ltrim(' 😉') as value; -- value '😉'
Supported Signatures
function ltrim(varchar) returns varchar
max
Returns the maximum value of all input values.
Examples:
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
millisecond
Returns the millisecond of the second from x.
Supported Signatures
function millisecond(time(p)) returns bigint
function millisecond(timestamp(p)) returns bigint
min
Returns the minimum value of all input values.
Examples:
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
minute
Returns the minute of the hour from x.
Supported Signatures
function minute(time(p)) returns bigint
function minute(timestamp(p)) returns bigint
mod
Returns the modulus (remainder) of n divided by m.
Supported Signatures
function mod(bigint, bigint) returns bigint
function mod(double, double) returns double
month
Returns the month of the year from x.
Examples:
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
now
This is an alias for current_timestamp.
Examples:
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.
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
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:
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
nullif
Returns null if value1 equals value2, otherwise returns value1.
Examples:
SELECT nullif(1, 1) AS value; -- value 'NULL'
SELECT nullif(2, 1) AS value; -- value '2'
Supported Signatures
function nullif($1, $1) returns $1
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:
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
pi
Returns the constant Pi.
Examples:
SELECT PI() -- value '3.141592653589793'
Supported Signatures
function pi() returns double
pow
This is an alias for power().
Supported Signatures
function pow(double, double) returns double
power
Returns x raised to the power of p.
Examples:
select power(2, 8) as value; -- value '256'
Supported Signatures
function power(double, double) returns double
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
radians
Converts angle x in degrees to radians.
Examples:
SELECT radians(360) AS value; -- value '6.283185307179586'
Supported Signatures
function radians(double) returns double
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.
random
Returns a pseudo-random value in the range 0.0 <= x < 1.0.
Examples:
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.
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:
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
regexp_count
Returns the number of occurrence of pattern in string.
Supported Signatures
function regexp_count(varchar, joniregexp) returns bigint
regexp_extract
Returns the first substring matched in a string by the regular expression pattern.
Examples:
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
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>
regexp_like
Evaluates the regular expression pattern and determines if it is contained within string.
Examples:
SELECT REGEXP_like('email_address@gmail.com', '.*@.*\..*') AS is_valid_email -- value 'TRUE'
Supported Signatures
function regexp_like(varchar, joniregexp) returns boolean
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
regexp_replace
Removes every instance of the substring matched by the regular expression pattern from string.
Examples:
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
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>
repeat
Repeat element for count times.
Examples:
select repeat('😉', 4) as value; -- value '😉😉😉😉'
Supported Signatures
function repeat($1, bigint) returns array<$1>
replace
Removes all instances of search from string.
Examples:
select replace('Hello There', 'There') as value; -- value 'Hello '
Supported Signatures
function replace(varchar, varchar, varchar) returns varchar
reverse
Returns string with the characters in reverse order.
Examples:
select reverse('Hello There') as value; -- value 'ereht olleH'
Supported Signatures
function reverse(array<$3>) returns array<$3>
function reverse(varchar) returns varchar
round
Returns x rounded to the nearest integer.
Examples:
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
row_number
Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition.
Examples:
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
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:
select rpad('😉', 5, '*') as value; -- value '😉****'
Supported Signatures
function rpad(varchar, bigint, varchar) returns varchar
rtrim
Removes trailing whitespace from string.
Examples:
select rtrim('😉 ') as value; -- value '😉'
Supported Signatures
function rtrim(varchar) returns varchar
second
Returns the second of the minute from x.
Supported Signatures
function second(time(p)) returns bigint
function second(timestamp(p)) returns bigint
sha256
Computes the SHA256 hash of binary.
Examples:
select sha256('Hello World!') as value; -- value '7f83b1657ff1fc53b92dc18148a1d65dfc2d4b1fa3d677284addd200126d9069'
Supported Signatures
function sha256(varbinary) returns varbinary
sha512
Computes the SHA512 hash of binary.
Examples:
select sha512('Hello World!') as value; -- value '861844d6704e8573fec34d967e20bcfef3d424cf48be04e6dc08f2bd58c729743371015ead891cc3cf1c9d34b49264b510751b1ff9e537937bc46b5d6ff4ecc8'
Supported Signatures
function sha512(varbinary) returns varbinary
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
sin
Returns the sine of x.
Examples:
select sin(2.1) as value; -- value '-0.8632093666488737'
Supported Signatures
function sin(double) returns double
sinh
Returns the hyperbolic sine of x.
Examples:
SELECT sinh(1) AS value; -- value '1.1752011936438014'
Supported Signatures
function sinh(double) returns double
split
Splits string on delimiter and returns an array.
Examples:
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>
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:
SELECT split_part('hello world', 'world', 1) as value; -- value 'hello'
Supported Signatures
function split_part(varchar, varchar, bigint) returns varchar
sqrt
Returns the square root of x.
Examples:
select sqrt(16) as value; -- value '4.0'
Supported Signatures
function sqrt(double) returns double
starts_with
Tests whether substring is a prefix of string.
Examples:
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
stddev
This is an alias for stddev_samp().
Supported Signatures
function stddev(bigint) returns double
function stddev(double) returns double
stddev_pop
Returns the population standard deviation of all input values.
Examples:
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
stddev_samp
Returns the sample standard deviation of all input values.
Examples:
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
strpos
Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.
Examples:
select strpos('Hello World', 'World') as value; -- value '7'
Supported Signatures
function strpos(varchar, varchar) returns bigint
substr
This is an alias for substring().
Examples:
select substr('Hello World', 6) as value; -- value ' World'
Supported Signatures
function substr(varchar, bigint) returns varchar
function substr(varchar, bigint, bigint) returns varchar
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:
select substring('Hello World', 6) as value; -- value ' World'
Supported Signatures
function substring(varchar, bigint) returns varchar
function substring(varchar, bigint, bigint) returns varchar
sum
Returns the sum of all input values.
Examples:
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
tan
Returns the tangent of x.
Examples:
select tan(0.5) as value; -- value '0.5463024898437905'
Supported Signatures
function tan(double) returns double
tanh
Returns the hyperbolic tangent of x.
Examples:
SELECT tanh(1.0) AS value; -- value '0.7615941559557649'
Supported Signatures
function tanh(double) returns double
to_hex
Encodes binary into a hex string representation.
Examples:
SELECT to_hex(cast(1000 as varbinary)) AS value; -- value 'e8030000'
Supported Signatures
function to_hex(varbinary) returns varchar
to_timestamp_seconds
Supported Signatures
function to_timestamp_seconds($1) returns timestamp
to_unixtime
Returns timestamp as a UNIX timestamp.
Examples:
SELECT to_unixtime(now()) AS value; -- value '1718974807'
Supported Signatures
function to_unixtime(timestamp(p)) returns double
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:
select translate('Hello World', 'Hell', 'N') as value; -- value 'No Word'
Supported Signatures
function translate(varchar, varchar, varchar) returns varchar
trim
Removes any leading and/or trailing characters as specified up to and including string from source.
Examples:
select trim(' 😉 ') as value; -- value '😉'
Supported Signatures
function trim(varchar) returns varchar
truncate
Returns x rounded to integer by dropping digits after decimal point.
Examples:
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
upper
Converts string to uppercase.
Examples:
select upper('Hello World!') as value; -- value 'HELLO WORLD!'
Supported Signatures
function upper(varchar) returns varchar
uuid
Returns a pseudo randomly generated UUID (type 4).
Examples:
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.
year
Returns the year from x.
Examples:
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