Functions

The below functions are currently supported. More can be added on request.

Function	Description	Arguments & Input Types	Output Type	Example
TRY_CAST	Attempts to cast a value to a specified type, returning null on failure.	(value: T, type: STRING)	T	SELECT TRY_CAST('123' AS INTEGER);
APPROX_DISTINCT	Returns the approximate number of distinct input values.	(expr: T)	BIGINT	SELECT APPROX_DISTINCT(column_name) FROM table_name;
ARRAY_DISTINCT	Removes duplicate values from an array.	(array: ARRAY<T>)	ARRAY<T>	SELECT ARRAY_DISTINCT(ARRAY[1, 2, 2, 3]);
AT_TIMEZONE	Converts a timestamp to the specified timezone.	(timestamp: TIMESTAMP, timezone: STRING)	TIMESTAMP	SELECT AT_TIMEZONE(TIMESTAMP '2023-10-01 12:00:00', 'America/New_York');
AVG	Returns the average value of the input expression.	(expr: T)	DOUBLE	SELECT AVG(column_name) FROM table_name;
CASE	Evaluates a list of conditions and returns the first matching result.	(conditions: BOOLEAN, results: T, ...)	T	SELECT CASE WHEN condition THEN result ELSE default END;
CAST	Converts a value to a specified type.	(value: T, type: STRING)	T	SELECT CAST('123' AS INTEGER);
CHR	Returns the character with the specified ASCII code.	(code: INTEGER)	STRING	SELECT CHR(65);
COALESCE	Returns the first non-null value in the list.	(value1: T, value2: T, ...)	T	SELECT COALESCE(NULL, 'default');
CONCAT	Concatenates two or more strings.	(string1: STRING, string2: STRING, ...)	STRING	SELECT CONCAT('Hello', ' ', 'World');
CONCAT_WS	Concatenates strings with a separator.	(separator: STRING, string1: STRING, ...)	STRING	SELECT CONCAT_WS(',', 'a', 'b', 'c');
COUNT	Returns the number of input rows for which the expression is not null.	(expr: T)	BIGINT	SELECT COUNT(column_name) FROM table_name;
COUNT(DISTINCT)	Returns the number of distinct input values.	(expr: T)	BIGINT	SELECT COUNT(DISTINCT column_name) FROM table_name;
DATE_ADD	Adds a specified time interval to a date.	(unit: STRING, value: INTEGER, date: DATE)	DATE	SELECT DATE_ADD('day', 1, DATE '2023-10-01');
DATE_DIFF	Returns the difference between two dates in specified units.	(unit: STRING, date1: DATE, date2: DATE)	BIGINT	SELECT DATE_DIFF('day', DATE '2023-10-02', DATE '2023-10-01');
DENSE_RANK	Assigns a rank to each row within a partition of a result set.	()	BIGINT	SELECT DENSE_RANK() OVER (ORDER BY column_name) FROM table_name;
ELEMENT_AT	Returns the element of an array at a specified index.	(array: ARRAY<T>, index: INTEGER)	T	SELECT ELEMENT_AT(ARRAY[1, 2, 3], 2);
FLATTEN	Flattens a nested array into a single array.	(array: ARRAY<ARRAY<T>>)	ARRAY<T>	SELECT FLATTEN(ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
GREATEST	Returns the greatest value among the arguments.	(value1: T, value2: T, ...)	T	SELECT GREATEST(1, 2, 3);
HASH	Returns a hash value for the given expression.	(expr: T)	BIGINT	SELECT HASH('example');
HISTOGRAM	Computes a histogram of the input values.	(expr: T)	MAP<T,BIGINT>	SELECT HISTOGRAM(column_name) FROM table_name;
JSON_EXTRACT	Extracts a JSON object from a JSON string based on a path.	(json: JSON, path: STRING)	JSON	SELECT JSON_EXTRACT('{"key": "value"}', '$.key');
JSON_EXTRACT_SCALAR	Extracts a scalar value from a JSON string based on a path.	(json: JSON, path: STRING)	STRING	SELECT JSON_EXTRACT_SCALAR('{"key": "value"}', '$.key');
JSON_FORMAT	Formats a JSON object as a string.	(json: JSON)	STRING	SELECT JSON_FORMAT(CAST('{"key": "value"}' AS JSON));
LAG	Returns the value of an expression from a preceding row.	(expr: T, offset: INTEGER, default: T)	T	SELECT LAG(column_name, 1) OVER (ORDER BY column_name) FROM table_name;
LAST_DAY_OF_MONTH	Returns the last day of the month for a given date.	(date: DATE)	DATE	SELECT LASTDAY(DATE '2023-10-01');
LEAD	Returns the value of an expression from a following row.	(expr: T, offset: INTEGER, default: T)	T	SELECT LEAD(column_name, 1) OVER (ORDER BY column_name) FROM table_name;
LEAST	Returns the least value among the arguments.	(value1: T, value2: T, ...)	T	SELECT LEAST(1, 2, 3);
LOWER	Converts a string to lowercase.	(string: STRING)	STRING	SELECT LOWER('HELLO');
MAP	Creates a map from the given key-value pairs.	(key: K, value: V)	MAP<K,V>	SELECT MAP(ARRAY['key1', 'key2'], ARRAY['value1', 'value2']);
MAP_CONCAT	Concatenates two maps into a single map.	(map1: MAP<K, V>, map2: MAP<K, V>)	MAP<K,V>	SELECT MAP_CONCAT(MAP(ARRAY['a'], ARRAY[1]), MAP(ARRAY['b'], ARRAY[2]));
MAP_ENTRIES	Returns an array of key-value pairs from a map.	(map: MAP<K, V>)	ARRAY<ROW>	SELECT MAP_ENTRIES(MAP(ARRAY['a'], ARRAY[1]));
MAP_FROM_ENTRIES	Creates a map from an array of key-value pairs.	(entries: ARRAY<ROW<K, V>>)	MAP<K,V>	SELECT MAP_FROM_ENTRIES(ARRAY[ROW('a', 1), ROW('b', 2)]);
MAP_KEYS	Returns an array of keys from a map.	(map: MAP<K, V>)	ARRAY<K>	SELECT MAP_KEYS(MAP(ARRAY['a'], ARRAY[1]));
MAP_VALUES	Returns an array of values from a map.	(map: MAP<K, V>)	ARRAY<V>	SELECT MAP_VALUES(MAP(ARRAY['a'], ARRAY[1]));
MAX	Returns the maximum value of the input expression.	(expr: T)	T	SELECT MAX(column_name) FROM table_name;
MAX_BY	Returns the value of the first argument for which the second argument is maximum.	(expr: T, weight: U)	T	SELECT MAX_BY(column_name, weight_column) FROM table_name;
MIN	Returns the minimum value of the input expression.	(expr: T)	T	SELECT MIN(column_name) FROM table_name;
MIN_BY	Returns the value of the first argument for which the second argument is minimum.	(expr: T, weight: U)	T	SELECT MIN_BY(column_name, weight_column) FROM table_name;
MODE	Returns the most frequent value in the input expression.	(expr: T)	T	SELECT MODE(column_name) FROM table_name;
POWER	Raises the base to the power of the exponent.	(base: DOUBLE, exponent: DOUBLE)	DOUBLE	SELECT POWER(2, 3);
RANK	Assigns a rank to each row within a partition of a result set.	()	BIGINT	SELECT RANK() OVER (ORDER BY column_name) FROM table_name;
REGEXP_EXTRACT	Extracts a substring matching a regular expression.	(string: STRING, pattern: STRING)	STRING	SELECT REGEXP_EXTRACT('abc123', '\\d+');
REGEXP_EXTRACT_ALL	Extracts all substrings matching a regular expression.	(string: STRING, pattern: STRING)	ARRAY<STRING>	SELECT REGEXP_EXTRACT_ALL('abc123', '\\d+');
REGEXP_LIKE	Checks if a string matches a regular expression pattern.	(string: STRING, pattern: STRING)	BOOLEAN	SELECT REGEXP_LIKE('abc123', '\\d+');
REGEXP_REPLACE	Replaces substrings matching a regular expression with a replacement.	(string: STRING, pattern: STRING, replacement: STRING)	STRING	SELECT REGEXP_REPLACE('abc123', '\\d+', '');
REVERSE	Reverses the characters in a string.	(string: STRING)	STRING	SELECT REVERSE('hello');
ROUND	Rounds a number to a specified number of decimal places.	(number: DOUBLE, decimals: INTEGER)	DOUBLE	SELECT ROUND(123.456, 2);
ROW_NUMBER	Assigns a unique number to each row within a partition of a result set.	()	BIGINT	SELECT ROW_NUMBER() OVER (ORDER BY column_name) FROM table_name;
SEQUENCE	Generates a sequence of numbers.	(start: BIGINT, stop: BIGINT, step: BIGINT)	ARRAY<BIGINT>	SELECT SEQUENCE(1, 10, 2);
SUBSTRING	Extracts a substring from a string.	(string: STRING, start: INTEGER, length: INTEGER)	STRING	SELECT SUBSTRING('hello', 1, 3);
SUM	Returns the sum of the input expression.	(expr: T)	T	SELECT SUM(column_name) FROM table_name;
TO_MILLISECONDS	Converts a timestamp to milliseconds since epoch.	(timestamp: TIMESTAMP)	BIGINT	SELECT TO_MILLISECONDS(TIMESTAMP '2023-10-01 12:00:00');
CARDINALITY	Returns the number of elements in an array.	(array: ARRAY<T>)	BIGINT	SELECT CARDINALITY(ARRAY[1, 2, 3]);
ARRAY_JOIN	Joins array elements into a single string with a delimiter.	(array: ARRAY<STRING>, delimiter: STRING, nullReplacement: STRING)	STRING	SELECT ARRAY_JOIN(ARRAY['a', 'b', 'c'], ',');
ARRAY_MAX	Returns the maximum value in an array.	(array: ARRAY<T>)	T	SELECT ARRAY_MAX(ARRAY[1, 2, 3]);
ARRAY_MIN	Returns the minimum value in an array.	(array: ARRAY<T>)	T	SELECT ARRAY_MIN(ARRAY[1, 2, 3]);
ARRAY_SORT	Sorts the elements of an array.	(array: ARRAY<T>)	ARRAY<T>	SELECT ARRAY_SORT(ARRAY[3, 1, 2]);
CONTAINS	Checks if an array contains a specified value.	(array: ARRAY<T>, value: T)	BOOLEAN	SELECT CONTAINS(ARRAY[1, 2, 3], 2);
SEQUENCE	Generates a sequence of numbers.	(start: BIGINT, stop: BIGINT, step: BIGINT)	ARRAY<BIGINT>	SELECT SEQUENCE(1, 10, 2);
VARIANCE	Returns the variance of the input expression.	(expr: T)	DOUBLE	SELECT VARIANCE(column_name) FROM table_name;
APPROX_PERCENTILE	Returns an approximate percentile of the input expression.	(expr: T, percentile: DOUBLE)	T	SELECT APPROX_PERCENTILE(column_name, 0.95) FROM table_name;
ARRAY_AGG	Aggregates all the input values into an array.	(expr: T)	ARRAY<T>	SELECT ARRAY_AGG(column_name) FROM table_name;
STDDEV	Returns the standard deviation of the input expression.	(expr: T)	DOUBLE	SELECT STDDEV(column_name) FROM table_name;
NULLIF	Returns null if the two values are equal, otherwise returns the first value.	(value1: T, value2: T)	T	SELECT NULLIF(column_name, 0) FROM table_name;
LTRIM	Removes leading whitespace from a string.	(string: STRING)	STRING	SELECT LTRIM(' hello')
RTRIM	Removes trailing whitespace from a string.	(string: STRING)	STRING	SELECT RTRIM('hello ');
ABS	Returns the absolute value of a number.	(number: DOUBLE)	DOUBLE	SELECT ABS(-5);
ANY_VALUE	Returns any value from the input expression.	(expr: T)	T	SELECT ANY_VALUE(column_name) FROM table_name;
ARRAY	Creates an empty array of the specified type.	()	ARRAY<T>	SELECT ARRAY();
CEIL	Rounds a number up to the nearest integer.	(number: DOUBLE)	DOUBLE	SELECT CEIL(3.14);
COUNTRY	Returns the country name for a given country code.	(code: STRING)	STRING	SELECT COUNTRY('US');
DATE_FORMAT	Formats a date according to the specified format string.	(date: DATE, format: STRING)	STRING	SELECT DATE_FORMAT(DATE '2023-10-01', '%Y-%m-%d');
DATE_TRUNC	Truncates a date to the specified precision.	(unit: STRING, timestamp: TIMESTAMP)	TIMESTAMP	SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-15 10:30:00');
FILTER	Filters elements from an array based on a lambda predicate.	(array: ARRAY<T>, lambda: T -> BOOLEAN)	ARRAY<T>	SELECT FILTER(ARRAY[1, 2, 3], x -> x > 1);
FLOOR	Rounds a number down to the nearest integer.	(number: DOUBLE)	DOUBLE	SELECT FLOOR(3.14);
FROM_UNIXTIME	Converts Unix timestamp to timestamp.	(unixtime: BIGINT)	TIMESTAMP	SELECT FROM_UNIXTIME(1633046400);
IF	Returns second argument if first is true, third argument otherwise.	(condition: BOOLEAN, true_value: T, false_value: T)	T	SELECT IF(x > 0, 'positive', 'negative');
LENGTH	Returns the length of a string.	(string: STRING)	INTEGER	SELECT LENGTH('hello');
NOW	Returns the current timestamp.	()	TIMESTAMP	SELECT NOW();
REDUCE	Reduces array elements to a single value using a lambda function.	(array: ARRAY<T>, initial: S, lambda: (S,T) -> S)	S	SELECT REDUCE(ARRAY[1, 2, 3], 0, (s, x) -> s + x);
REPLACE	Replaces all occurrences of search string with replacement string.	(string: STRING, search: STRING, replacement: STRING)	STRING	SELECT REPLACE('hello world', 'world', 'there');
SHIFT_BACKWARD	Shifts timestamp backward by specified interval.	(timestamp: TIMESTAMP, interval: STRING)	TIMESTAMP	SELECT SHIFT_BACKWARD(TIMESTAMP '2023-10-01 12:00:00', '1 day');
SHIFT_FORWARD	Shifts timestamp forward by specified interval.	(timestamp: TIMESTAMP, interval: STRING)	TIMESTAMP	SELECT SHIFT_FORWARD(TIMESTAMP '2023-10-01 12:00:00', '1 day');
SPLIT	Splits string into array using delimiter.	(string: STRING, delimiter: STRING)	ARRAY<STRING>	SELECT SPLIT('a,b,c', ',');
SQRT	Returns the square root of a number.	(number: DOUBLE)	DOUBLE	SELECT SQRT(16);
TO_UNIXTIME	Converts timestamp to Unix timestamp.	(timestamp: TIMESTAMP)	BIGINT	SELECT TO_UNIXTIME(TIMESTAMP '2023-10-01 12:00:00');
TRANSFORM	Transforms array elements using a lambda function.	(array: ARRAY<T>, lambda: T -> U)	ARRAY<U>	SELECT TRANSFORM(ARRAY[1, 2, 3], x -> x * 2);
UPPER	Converts a string to uppercase.	(string: STRING)	STRING	SELECT UPPER('hello');