abs
Returns the absolute value of x.
Examples:
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:
select acos(0.5) as value;
Supported Signatures
function acos(double) returns double
🔗 Official Documentation
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;
Supported Signatures
function array_agg($1) returns array<$1>
🔗 Official Documentation
array_distinct
Remove duplicate values from the array x.
Examples:
SELECT array_distinct([1, 2, 3]) as value;
SELECT array_distinct([1, 1, 2, 3]) as value;
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:
SELECT array_except([1, 1, 2, 3], [1]) AS value;
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:
SELECT array_intersect([1, 1, 2, 3], [1]) AS value;
SELECT array_intersect(['hello', 'world'], ['hello']) AS value;
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:
SELECT array_join(['hello', 'world'], ' beautiful ') AS value;
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:
SELECT array_position([1, 1, 2], 1) AS value;
Supported Signatures
function array_position(array<$1>, $1) returns bigint
🔗 Official Documentation
array_remove
Remove all elements that equal element from array x.
Examples:
SELECT array_remove([1, 1, 2], 1) AS value;
SELECT array_remove(['hello', 'bad', 'world'], 'bad') AS value;
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:
SELECT array_union([1, 1], [2]) as value;
Supported Signatures
function array_union(array<$3>, array<$3>) returns array<$3>
🔗 Official Documentation
asin
Returns the arc sine of x.
Examples:
Supported Signatures
function asin(double) returns double
🔗 Official Documentation
atan
Returns the arc tangent of x.
Examples:
Supported Signatures
function atan(double) returns double
🔗 Official Documentation
atan2
Returns the arc tangent of y / x.
Examples:
select atan2(1, 0.5) as value;
Supported Signatures
function atan2(double, double) returns double
🔗 Official Documentation
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;
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:
SELECT cardinality([1, 1, 2]) as value;
Supported Signatures
function cardinality(array<$3>) returns bigint
🔗 Official Documentation
cbrt
Returns the cube root of x.
Examples:
SELECT cbrt(328509) as value;
Supported Signatures
function cbrt(double) returns double
🔗 Official Documentation
ceil
This is an alias for ceiling().
Examples:
SELECT ceil(1.3) AS value;
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:
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:
CREATE TABLE tbl as VALUES ('1', null), ('2', '2'), (null, '3');
SELECT
COALESCE(column1, column2) as value
FROM tbl;
Supported Signatures
function coalesce($1, ...) returns $1
🔗 Official Documentation
codepoint
Returns the Unicode code point of the only character of string.
Examples:
SELECT codepoint('😊') AS value;
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:
select concat('hello ', 'world!') as value;
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:
SELECT concat_ws('😊', 'hey', 'there', 'people') AS value;
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:
SELECT contains(['hello', 'world'], 'hello') AS value;
SELECT contains(['hello', 'world'], '😊') AS value;
Supported Signatures
function contains(array<$1>, $1) returns boolean
🔗 Official Documentation
cos
Returns the cosine of x.
Examples:
select cos(1.0) as value;
Supported Signatures
function cos(double) returns double
🔗 Official Documentation
cosh
Returns the hyperbolic cosine of the given value.
Examples:
Supported Signatures
function cosh(double) returns double
🔗 Official Documentation
count
Returns the number of input rows.
Examples:
CREATE TABLE tbl AS VALUES (1), (5), (9);
SELECT count(tbl.column1) AS value FROM tbl;
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:
SELECT current_date() AS value;
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:
SELECT current_time() AS value;
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:
SELECT current_timestamp() AS value;
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:
SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00') AS value;
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC') AS value;
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02') AS value;
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789') AS value;
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789') AS value;
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
Formats timestamp as a string using format.
Examples:
SELECT date_format(TIMESTAMP '2022-10-20 05:10:00', '%m-%d-%Y %H') AS value;
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:
SELECT day(date('2022-10-20')) AS value;
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:
SELECT degrees(3.14159) AS value;
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:
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)
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:
select exp(1.0) as value;
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:
SELECT flatten(
ARRAY [ARRAY [1, 2],
ARRAY [3, 4]])
AS value;
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:
SELECT from_unixtime(999919900) AS value;
Supported Signatures
function from_unixtime(bigint) returns timestamp
🔗 Official Documentation
greatest
Returns the largest of the provided values.
Examples:
SELECT greatest(1, 2, 3) AS value;
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
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;
Supported Signatures
function if(boolean, $1, $1) returns $1
🔗 Official Documentation
index
Alias for strpos() function.
Examples:
SELECT INDEX('abcdefg', 'c')
Supported Signatures
function index(varchar, varchar) returns bigint
🔗 Official Documentation
is_nan
Determine if x is not-a-number.
Examples:
select is_nan(1.1) as value;
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:
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)
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:
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)
Supported Signatures
function lead($1) returns $1
🔗 Official Documentation
least
Returns the smallest of the provided values.
Examples:
SELECT LEAST(5,6,7,1,2,3,4)
Supported Signatures
function least($3, ...) returns $3
🔗 Official Documentation
length
Returns the length of string in characters.
Examples:
select length('Hello World!') as value;
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
Returns the natural logarithm of x.
Examples:
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:
SELECT log(2.19, 1) AS value;
Supported Signatures
function log(double, double) returns double
🔗 Official Documentation
log10
Returns the base 10 logarithm of x.
Examples:
select log10(10) as value;
Supported Signatures
function log10(double) returns double
🔗 Official Documentation
log2
Returns the base 2 logarithm of x.
Examples:
Supported Signatures
function log2(double) returns double
🔗 Official Documentation
lower
Converts string to lowercase.
Examples:
select lower('Hello World!') as value;
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:
select lpad('😉', 5, '*') as value;
Supported Signatures
function lpad(varchar, bigint, varchar) returns varchar
🔗 Official Documentation
ltrim
Removes leading whitespace from string.
Examples:
select ltrim(' 😉') as value;
Supported Signatures
function ltrim(varchar) returns varchar
🔗 Official Documentation
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;
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:
CREATE TABLE tbl AS VALUES (1), (5), (9);
SELECT min(tbl.column1) AS value FROM tbl;
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:
SELECT month(cast ('2024-11-01' AS date )) AS value;
Supported Signatures
function month(date) returns bigint
function month(timestamp(p)) returns bigint
🔗 Official Documentation
now
This is an alias for current_timestamp.
Examples:
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:
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)
Supported Signatures
function ntile(bigint) returns bigint
🔗 Official Documentation
nullif
Returns null if value1 equals value2, otherwise returns value1.
Examples:
SELECT nullif(1, 1) AS value;
SELECT nullif(2, 1) AS value;
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:
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)
Supported Signatures
function percent_rank() returns double
🔗 Official Documentation
Returns the constant Pi.
Examples:
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:
select power(2, 8) as value;
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:
SELECT radians(360) AS value;
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:
select random() as value;
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:
SELECT name, RANK() OVER (ORDER BY score) AS rank_col FROM (
VALUES ('A', 10), ('B', 20), ('C', 20), ('D', 30)
) AS t (name, score)
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
Returns the first substring matched in a string by the regular expression pattern.
Examples:
SELECT REGEXP_EXTRACT('email_address@gmail.com', '@(.*?)\.'
Supported Signatures
function regexp_extract(varchar, joniregexp) returns varchar
function regexp_extract(varchar, joniregexp, bigint) returns varchar
🔗 Official Documentation
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:
SELECT REGEXP_like('email_address@gmail.com', '.*@.*\..*') AS is_valid_email
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:
SELECT REGEXP_REPLACE('text foo another text', 'foo', 'bar')
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:
select repeat('😉', 4) as value;
Supported Signatures
function repeat($1, bigint) returns array<$1>
🔗 Official Documentation
replace
Removes all instances of search from string.
Examples:
select replace('Hello There', 'There') as value;
Supported Signatures
function replace(varchar, varchar, varchar) returns varchar
🔗 Official Documentation
reverse
Returns string with the characters in reverse order.
Examples:
select reverse('Hello There') as value;
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:
select round(2.1) as value;
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:
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)
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:
select rpad('😉', 5, '*') as value;
Supported Signatures
function rpad(varchar, bigint, varchar) returns varchar
🔗 Official Documentation
rtrim
Removes trailing whitespace from string.
Examples:
select rtrim('😉 ') as 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:
select sha256('Hello World!') as value;
Supported Signatures
function sha256(varbinary) returns varbinary
🔗 Official Documentation
sha512
Computes the SHA512 hash of binary.
Examples:
select sha512('Hello World!') as value;
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:
select sin(2.1) as value;
Supported Signatures
function sin(double) returns double
🔗 Official Documentation
sinh
Returns the hyperbolic sine of x.
Examples:
Supported Signatures
function sinh(double) returns double
🔗 Official Documentation
split
Splits string on delimiter and returns an array.
Examples:
SELECT split('hello+world', '+') as value;
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:
SELECT split_part('hello world', 'world', 1) as value;
Supported Signatures
function split_part(varchar, varchar, bigint) returns varchar
🔗 Official Documentation
sqrt
Returns the square root of x.
Examples:
select sqrt(16) as value;
Supported Signatures
function sqrt(double) returns double
🔗 Official Documentation
starts_with
Tests whether substring is a prefix of string.
Examples:
SELECT starts_with('hello world', 'world')
SELECT starts_with('hello world', 'hello') ;
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:
CREATE TABLE tbl AS VALUES (1), (5), (9); SELECT stddev_pop(column1) as value from tbl;
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:
CREATE TABLE tbl AS VALUES (1), (5), (9); SELECT stddev_samp(column1) as value from tbl;
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:
select strpos('Hello World', 'World') as value;
Supported Signatures
function strpos(varchar, varchar) returns bigint
🔗 Official Documentation
substr
This is an alias for substring().
Examples:
select substr('Hello World', 6) as value;
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:
select substring('Hello World', 6) as value;
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:
CREATE TABLE tbl AS VALUES (1), (5), (9);
SELECT sum(tbl.column1) AS value FROM tbl;
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:
select tan(0.5) as value;
Supported Signatures
function tan(double) returns double
🔗 Official Documentation
tanh
Returns the hyperbolic tangent of x.
Examples:
SELECT tanh(1.0) AS value;
Supported Signatures
function tanh(double) returns double
🔗 Official Documentation
to_hex
Encodes binary into a hex string representation.
Examples:
SELECT to_hex(cast(1000 as varbinary)) AS value;
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:
SELECT to_unixtime(now()) AS value;
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:
select translate('Hello World', 'Hell', 'N') as value;
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:
select trim(' 😉 ') as value;
Supported Signatures
function trim(varchar) returns varchar
🔗 Official Documentation
truncate
Returns x rounded to integer by dropping digits after decimal point.
Examples:
SELECT truncate(1.23456, 2) as value;
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:
select upper('Hello World!') as value;
Supported Signatures
function upper(varchar) returns varchar
🔗 Official Documentation
uuid
Returns a pseudo randomly generated UUID (type 4).
Examples:
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:
SELECT year(cast('2024-01-01' as date)) as value;
Supported Signatures
function year(date) returns bigint
function year(timestamp(p)) returns bigint
🔗 Official Documentation
