Summary

All Functions (Alphabetical)

This page lists all Rockset SQL functions in alphabetical order.

FunctionDescription
ABS(x)Returns absolute value of x. Return type is the same as input.
ACOS(x)Computes the arc cosine of x.
ACOSH(x)Computes the inverse hyperbolic cosine of x.
APPROX_DISTINCT(x[, e])Returns the approximate number of distinct elements with a non-null value for field x.
ARBITRARY(x)Returns an arbitrary non-null element from the input. Returns null if the input is empty or all null.
ARRAY_AGG(x)Returns an array created from all the elements in x.
ARRAY_CONCAT(array1, array2, ...)Returns the concatenation of the input arrays.
ARRAY_CONTAINS(array, element)Returns true if array contains the element.
ARRAY_CONTAINS_ANY(array1, array2)Returns true if array1 and array2 have any elements in common.
ARRAY_CREATE(val1, val2, ...)Construct an array from literals.
ARRAY_DISTINCT(array)Returns an array with all duplicates removed.
ELEMENT_AT(array, val)Returns element of array at index val. If val < 0, this function accesses elements from the last to the first.
ARRAY_EXCEPT(array1, array2)Returns an array of elements in array1 but not in array2, without duplicates.
ARRAY_FLATTEN(array)Flattens an array(array(T)) to array(T) by concatenating the contained arrays. Each element in the array must be of array type.
ARRAY_INTERSECT(array1, array2)Returns an intersection of the two arrays, with all duplicates removed.
ARRAY_JOIN(array, delimiter, nullReplacement)Concatenates the elements of array using delimiter and an optional nullReplacement string to replace nulls. Accepts only string types.
ARRAY_LAST_POSITION(array, val)Return a 1-based index of the last occurrence of val if it is found within array. If val does not exist within array, it returns 0.
ARRAY_MAP(function_name, array)Given the name of a function as a string, and an array, apply the function to each element of the array. The function must be a scalar function, and must accept exactly one argument.
ARRAY_MAX(array)Returns an element which is greater than or equal to all other elements of the array. Returns null if one of the array elements is null.
ARRAY_MIN(array)Returns an element which is less than or equal to all other elements of the array. Returns null if one of the array elements is null.
ARRAY_POSITION(array, val)Return a 1-based index of the first occurrence of val if it is found within array. If val is null, it will look for occurrence of null in the array. If val does not exist within array, it returns 0.
ARRAY_REMOVE(array, val)Returns the array with all occurrences of value removed.
ARRAY_SHUFFLE(array)Returns a shuffled copy of the input array in any order.
ARRAY_SORT(array)Returns a sorted copy of the input array.
ARRAY_UNION(array1, array2)Returns a union of the two arrays, with all duplicates removed.
ASCII(string)Returns the Unicode code point value of the first character in string, or zero if the string is empty.
ASIN(x)Computes the arc sine of x.
ASINH(x)Computes the inverse hyperbolic sine of x.
ATAN(x)Computes the arc tangent of x.
ATAN2(y, x)Computes the arc tangent of y / x, but with proper sign for quadrant correction. That is, correctly computes the angle θ when converting from the Cartesian coordinates (x, y) to the polar coordinates (r, θ).
ATANH(x)Computes the inverse hyperbolic tangent of x.
AVG(x)Returns an average of all the elements in x. All elements are implicitly promoted to float. Return value is of type float.
BITWISE_AND(x, y)Returns the bitwise AND of x and y in 2’s complement representation.
BITWISE_AND_AGG(x)Returns the bitwise AND of all input values in 2's complement representation.
BITWISE_NOT(x)Returns the bitwise NOT of x in 2’s complement representation.
BITWISE_OR(x, y)Returns the bitwise OR of x and y in 2’s complement representation.
BITWISE_OR_AGG(x)Returns the bitwise OR of all input values in 2's complement representation.
BITWISE_XOR(x, y)Returns the bitwise XOR of x and y in 2’s complement representation.
BIT_COUNT(x, bits)Count the number of bits set in x (treated as bits-bit signed integer) in 2’s complement representation.
BOOL_AND(x)Returns true if every value in the input is true, false otherwise. Return value and all arguments are boolean.
BOOL_OR(x)Returns true if one value in the input is true, false otherwise. Return value and all arguments are boolean.
BOOST(boost_value, term)Set the boost value (a positive floating point value) for a specific search term so that the term contributes the specified value. Unboosted terms contribute a default score of 1.0. Note that following sample queries use collection data described at the top of this page.
CARDINALITY(array)Alias of LENGTH.
CASECASE is like an IF-THEN-ELSE statement. It executes a series of conditions and returns the value corresponding to the condition that evaluated to true.
CAST(x AS type)Lexical cast that supports casting between more types than STATIC CAST. Errors the query if the cast is not supported.
CEIL(x)Returns the smallest integral value that is not less than x.
CEILING(x)Alias of CEIL.
CHR(n)Returns a single character string made up of the character whose Unicode code point is n.
COALESCE(x, ...)Returns the first non-null value in the argument list.
CONCAT(x, ...)Returns the concatenation of the strings given in the arguments. Supports variable number of arguments. null arguments are ignored.
ST_CONTAINS(geography_a, geography_b)Returns true if and only if geography_b is entirely contained within geography_a.
ACOS(x)Computes the arc cosine of x.
ACOSH(x)Computes the inverse hyperbolic cosine of x.
COSINE_SIM(array, array)Computes the cosine similarity of two vectors. Cosine similarity represents the dot product of two vectors divided by their magnitude
COUNT(*)Returns the number of input rows.
COUNT_IF(x)Returns the number of elements in x which are true.
CUME_DIST()Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).
CURRENT_DATE([timezone])Returns current date in the timezone time zone (default UTC). Return value is of date type.
CURRENT_DATETIME([timezone])Returns current date and time in the timezone time zone (default UTC). Return value is of datetime type.
CURRENT_TIME([timezone])Returns current time in the timezone time zone (default UTC). Return value is of time type.
CURRENT_TIMESTAMP()Returns the current timestamp. As the timestamp refers to an absolute moment in time, no time zone argument is necessary (or allowed). The returned value is of the timestamp type.
CURRENT_DATE([timezone])Returns current date in the timezone time zone (default UTC). Return value is of date type.
CURRENT_DATETIME([timezone])Returns current date and time in the timezone time zone (default UTC). Return value is of datetime type.
DATE_DIFF(precision, start_timestamp, end_timestamp)Computes the difference between start_timestamp and end_timestamp at the precision level provided. The difference is truncated to the nearest integer.
DATE_FROM_UNIX_DATE(number)Returns a date from the given number, the number of days since January 1st, 1970.
DATE_PARSE(string, format)Parses the date string (formatted using the given format) into a date value.
DATE_TRUNC(precision, timestamp)Truncates the provided timestamp (assumed at timezone UTC) to the precision level provided. To use an alternate timezone use AT TIME ZONE.
DAYS(n)Constructs a microsecond_interval value that refers to a duration of n days.
DEC128_FORMAT(low, high)Converts from the IEEE 754 decimal128 floating point format to a string using scientific notation. low and high should be integers holding the bottom and top 64 bits of the binary representation, respectively.
DENSE_RANK()Rank of the current row without gaps; this function counts peer groups.
DOT_PRODUCT(array, array)Computes the dot product of two vectors.
DURATION_MICROS(interval)Returns the duration of a microsecond_interval in microseconds.
DURATION_MILLIS(interval)Returns the duration of a microsecond_interval in milliseconds.
PARSE_DURATION_MONTHS(s)Parses a month_interval value from a string.
PARSE_DURATION_SECONDS(s)Parses a microsecond_interval value from a string.
ELEMENT_AT(array, val)Returns element of array at index val. If val < 0, this function accesses elements from the last to the first.
ERASE(obj, k)If k is a string, return a new object where the key k is erased. If k is an array of strings, return a new object where the keys in k are erased.
EUCLIDEAN_DIST(array, array)Computes the Euclidean distance of two vectors (which is also referred to as the L2 norm). Euclidean distance represents the square root of the sum of squared differences between corresponding elements of two vectors.
EVERY(x)An alias of BOOL_AND function.
EXP(x)Computes e to the power of x.
REGEXP_EXTRACT(string, pattern[, group])Returns the first match of pattern in string, or null if the pattern does not match. If group is specified and greater than zero, returns the groupth capturing group; if group is not specified or is zero, returns the full match.
FIRST_VALUE(value)Returns value evaluated at the row that is the first row of the window frame.
FLOOR(x)Returns the largest integral value that is not greater than x.
FORMAT(format_str, ...)Formats the arguments according to the format string. Will work with integers, floating point numbers, and strings. Follows the format string syntax of fmt: https://fmt.dev/latest/syntax.html
FORMAT_DATE(format, date)Converts date to string formatted using the given format.
FORMAT_DATETIME(format, datetime)Converts datetime to string using the given format.
FORMAT_TIME(format, time)Converts time to string using the given format.
FORMAT_TIMESTAMP(format, timestamp[, timezone]Converts timestamp to string using the given format, as of the given timezone (default UTC).
FROM_BASE(string, base)Returns the value of string interpreted as a number in base.
FROM_BASE64(s)Decodes the base64 string s into a bytes value.
FROM_HEX(s)Decodes the hex string s into a bytes value.
FROM_UTF8(b)If b is a bytes value that represents a valid UTF-8 string, return it as a string. Otherwise, raise an error.
GREATEST(a, b, c, ...)Returns the argument that is greater than or equal to all other arguments.
GROUPING(col1, col1, ... col_n)Returns the grouping mask, which is a bitmask associating one bit with every column (the first column in the list of arguments corresponds to the most significant bit in the result).
HASH(x, digest)Computes the hash of the bytes value x using digest, where digest is one of the OpenSSL digest methods md5, sha1, sha256, or sha512 (case insensitive).
HASH_COMBINE(x, ...)Computes a unique hash over all input arguments. Order matters for the input arguments, and all types are supported, though any null argument results in a null return value.
HAS_TERM(field, term)A term matcher that lets you search documents where the specified field contains the specified term. Upon a successful match, this matcher contributes a score of 1. Note that following sample queries use collection data described at the top of this page.
HMAC(x, key, digest)Computes the HMAC of the bytes value x using key as the key and digest as the digest method, where digest is is one of the OpenSSL digest methods md5, sha1, sha256, or sha512 (case insensitive).
HMAC_MD5(x, key)Computes the MD5 HMAC of the bytes value x using key as the key.
HMAC_SHA1(x, key)Computes the SHA1 HMAC of the bytes value x using key as the key.
HMAC_SHA256(x, key)Computes the SHA256 HMAC of the bytes value x using key as the key.
HMAC_SHA512(x, key)Computes the SHA512 HMAC of the bytes value x using key as the key.
HMAP_AGG(key, value, op)Creates a histogram map aggregate. For each input document, the op argument specifies the update semantics for the given key value pair.
HMAP_CONTAINS_VALUE(hmap, v)Returns whether value v occurs in the histogram map aggregate hmap.
HMAP_ELEMENT_AT(hmap, k)Returns the value associated with key k in the histogram map aggregate hmap.
HMAP_VALUE_COUNT(hmap, v)Returns the number of times value v occurs in the histogram map aggregate hmap.
HOURS(n)Constructs a microsecond_interval value that refers to a duration of n hours.
HYPOT(x, y)Computes SQRT(x*x + y*y), that is, the length of the hypothenuse of a right-angled triangle with sides of lengths x and y. This is also the distance between the point at coordinates (x, y) and origin.
ID_HASH(x, ...)Equivalent of TO_BASE64(HASH_COMBINE(args...)). Useful when constructing a custom value for _id in an ingest transformation, e.g. SELECT ID_HASH(field1, field2) AS _id.
IF(cond, x, y)Returns x if cond is true, y otherwise.
IS DISTINCT FROMReturns true if x is not equal to y, and false otherwise.
IS NULLReturns true if x is null or undefined.
IS UNDEFINEDReturns true if x is undefined.
IS_NAN(x)Returns true if the input is a floating point Not-A-Number, for instance, due to dividing zero by zero or taking the square root of a negative number. Non-numeric inputs yield null.
IS_SCALAR(x)Returns true if x is a scalar, which is any type except array, object, undefined and null.
ITEMS(obj)Return an array containing the entries of obj. Each entry is a 2-element array; the first is the key, the second is the value. The order is unspecified, but will be the same between KEYS(), VALUES(), and ITEMS().
JSON_FORMAT(x)Converts given JSON to string.
JSON_PARSE(x)Parses given string as JSON.
KEYS(obj)Return an array containing the keys of obj. The order is unspecified, but will be the same between KEYS(), VALUES(), and ITEMS().
LAG(value [, offset [, default ]])Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.
LAST_VALUE(value)Returns value evaluated at the row that is the last row of the window frame.
LEAD(value [, offset [, default ]])Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.
LEAST(a, b, c, ...)Returns the argument that is less than or equal to all other arguments.
LENGTH(obj)Returns number of elements in obj.
LENGTH(obj)Returns number of elements in obj.
LENGTH(obj)Returns number of elements in obj.
REGEXP_LIKE(string, pattern)Returns true if string matches the regular expression pattern, false otherwise.
LN(x)Computes the natural logarithm of x.
LOG(x)Alias of LN.
LOG10(x)Computes the base-10 logarithm of x.
LOG2(x)Computes the base-2 logarithm of x.
LOWER(x)Returns locale-independent lowercase string x.
LPAD(string, targetLength, padString)Pads string from the left to targetLength using padString. If targetLength is less than the length of string, the result will be truncated to the targetLength.
LTRIM(string [, characters])Remove the longest string containing only characters from characters (a space by default) from the start of string
MAP_AGG(keys, values)Creates an object where the keys are from the first input and the values are from the second input.
MAX(x)Returns the maximum value of all elements in x.
MAX_BY(x, y)Returns the value of column x associated with the maximum value of column y.
MD5(x)Computes the MD5 hash of the bytes value x.
MERGE(a, b)Return a new object containing the values from a and b. If the same key exists in both a and b, the value from b will overwrite the value from a. For example if collection X has the form
MICROSECONDS(n)Constructs a microsecond_interval value that refers to a duration of n microseconds.
MILLISECONDS(n)Constructs a microsecond_interval value that refers to a duration of n milliseconds.
MIN(x)Returns the minimum value of all elements in x.
MINUTES(n)Constructs a microsecond_interval value that refers to a duration of n minutes.
MIN_BY(x, y)Returns the value of column x associated with the minimum value of column y.
MONTHS(n)Constructs a month_interval value that refers to a duration of n months.
NGRAMS(string, n_length)Returns an array of all ngrams of string of length n_length. The max length allowed for the input string is 100,000.
NGRAMS(string, min_length, max_length)Returns an array of all ngrams of string that are between min_length and max_length in size. The max length allowed for the input string is 100,000.
NORMALIZE(string[, form])Returns Unicode-normalized form of string. form is an identifier and must be one of NFC, NFD, NFKC, NFKD, which are the four Unicode normalization methods; NFC is default.
NTH_VALUE(value, nth)Returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.
NTILE(num_buckets)Integer ranging from 1 to the argument value, dividing the partition as equally as possible.
NULLIF(value1, value2)Returns null if value1 equals value2, otherwise returns value1.
OBJECT(keys, values)Construct an object from an array of keys and an array of values. keys must be an array of strings. values must be an arbitrary array of the same length as keys.
PARSE_DATE(format, string)Parses the date string (formatted using the given format) into a date value.
PARSE_DATETIME(format, string)Parses the date string (formatted using the given format) into a datetime value.
PARSE_DATETIME_ISO8601(string)Parse a datetime from an ISO 8601 string without a timezone.
PARSE_DATE_ISO8601(string)Parses a date from an ISO 8601 string without a timezone.
PARSE_DURATION_MONTHS(s)Parses a month_interval value from a string.
PARSE_DURATION_SECONDS(s)Parses a microsecond_interval value from a string.
PARSE_TIME(format, string)Parses the date string (formatted using the given format) into a time value.
PARSE_TIMESTAMP(format, string[, default_timezone])Parses the date string (formatted using the given format) into a timestamp value. If the format string does not contain a timezone, default_timezone is used (UTC if not specified).
PARSE_TIMESTAMP_ISO8601(string)Parses the timestamp from an ISO 8601 string. The string must include a timezone offset (or the Z suffix for UTC).
PARSE_TIME_ISO8601(string)Parses a time from an ISO 8601 string without a timezone.
PERCENT_RANK()Relative rank of the current row: (rank - 1) / (total rows - 1).
PG_ARRAY_PARSE(x, typestr)Parses a string encoded using Postgres array output syntax. typestr should be a string holding the Postgres array type, such as 'integer[]' or 'text[]'. Int, float, and string arrays of any depth can be decoded.
POW(x, y)Computes x to the power of y.
POWER(x, y)Alias of POW.
PREFIXES(string, max_length)Returns an array of all prefixes of string whose length is less than or equal to max_length. If not specified, the default max_length used is 100. The max length allowed for the input string is 100,000.
PROXIMITY(term_matcher[, term_matcher]*)Create a proximity range matcher using the given term matchers. The term matchers will contribute 1 point to the final score, just like other term matchers in the SEARCH query. But the proximity range mather will also contribute a proximity score that depends on the longest consecutive match. The longest consecutive match is the longest sequence of N consecutive terms such that: - term at index T satisfies term matcher at index P - term at index T+1 satisfies term matcher at index P+1 - ... - term at index T+N-1 satisfies term matcher at index P+N-1 Based on the length N of the longest consecutive match, the proximity score is computed as ((N - 1) / 2). Note that following sample queries use collection data described at the top of this page.
PROXIMITY_BOOST(boost_value, proximity_range)Set the proximity range boost value (a positive floating point value) for the given specific search proximity range. This affects the proximity range's contribution to the score i.e. the proximity score, but not the contributions of the individual terms in the range. proximity_range must be a proximity range created using either PROXIMITY or CONTAINS. Note that following sample queries use collection data described at the top of this page.
RAND()Returns a pseudo-random value in the range [0.0, 1.0).
RANK()Rank of the current row with gaps; same as row_number of its first peer.
REGEXP_EXTRACT(string, pattern[, group])Returns the first match of pattern in string, or null if the pattern does not match. If group is specified and greater than zero, returns the groupth capturing group; if group is not specified or is zero, returns the full match.
REGEXP_EXTRACT_ALL(string, pattern[, group])Returns an array containing all matches of pattern in string. If group is specified and greater than zero, extracts the groupth capturing group from each match; if group is not specified or is zero, returns the full matches.
REGEXP_LIKE(string, pattern)Returns true if string matches the regular expression pattern, false otherwise.
REGEXP_REPLACE(string, pattern[, replacement])Returns string with all places where pattern matches replaced with replacement (or erased if replacement is not specified). replacement may use \1 .. \9 escape sequences to refer to capturing groups, or \0 to refer to the entire match.
REGEXP_SPLIT(string, pattern)Returns an array with the components of string when split by pattern.
REPEAT(val, count)Constructs an array of val repeated count times. count must be an integer.
REPLACE(string, search[, replacement])Returns a string with all instances of search replaced with replacement in string. replacement is optional, which if not specified removes all instance of search from string. When search is an empty string, string is not modified in any way.
REVERSE(string)Returns string with its characters in reverse order.
ROUND(x)Returns the integral value that is nearest to x, with halfway cases rounded away from zero.
ROW_NUMBER()Number of the current row within its partition, counting from 1.
RPAD(string, targetLength, padString)Pads string from the right to targetLength using padString. If targetLength is less than the length of string, the result will be truncated to the targetLength.
RTRIM(string[, characters])Remove the longest string containing only characters from characters (a space by default) from the end of string
SEARCH(term_matcher[, term_matcher]*) [OPTION(match_all=false)]Return documents that satisfy all of the term matchers (by default), or at least one of the term matchers (with OPTION(match_all=false)). Term matchers are arbitrary SQL boolean expressions, optionally boosted. The score of the match is the sum of the boost values (default 1) for all terms that matched. For searches that include proximity ranges (see below), each proximity range also adds to the score a proximity score that depends on the longest consecutive match.
TIMESTAMP_SECONDS(n)Constructs value of type timestamp from n seconds since the Unix epoch.
SEQUENCE(start, stop[, step])Constructs an array from start to stop with each value increasing or decreasing by step. If step is not provided, it defaults to 1 if start is less than stop, or -1 if start is greater than stop. start, stop, and step must all be integers.
SHA1(x)Computes the SHA1 hash of the bytes value x.
SHA256(x)Computes the SHA256 hash of the bytes value x.
SHA512(x)Computes the SHA512 hash of the bytes value x.
SIGN(x)Returns sign of x as an integer: -1 if x is negative, 0 if x is zero, 1 if x is positive.
ASIN(x)Computes the arc sine of x.
ASINH(x)Computes the inverse hyperbolic sine of x.
SLICE(array, start[, length])Returns a subset of array starting from index start (or starting from the end if start is negative) with length length (unless start + length > LENGTH(array)). length is optional and defaults to length of array if unspecified. If ABS(index) > LENGTH(array) it will throw an error.
SPLIT(string, delimiter[, limit])Splits string on delimiter and returns an array. With limit, only the first limit - 1 delimiters are split upon, thereby returning an array of size at most limit. The last element in the array always contains everything left in the string in the case where there are >= limit occurrences of the delimiter in the string. limit must be a positive number.
SPLIT_PART(string, delimiter, index)Splits string on delimiter and returns the field index. Field indexes start with 1. If the index is larger than than the number of fields, then null is returned.
SQRT(x)Computes the square root of x.
STATIC_CAST(x AS type)Static cast that converts values from one data type to another. Supports casts between numeric types and casts to bool with Python semantics. Errors the query if the cast is not supported.
STDDEV_SAMP(x)Returns the sample standard deviation of all input values.
STRPOS(string, substring)Returns the starting position of the first instance of substring in string. Positions start with 1. If not found, 0 is returned.
ST_ASTEXT(geography)Converts a geography to a well known text.
ST_CONTAINS(geography_a, geography_b)Returns true if and only if geography_b is entirely contained within geography_a.
ST_DISTANCE(geography_a, geography_b)Returns the distance, in meters, between the closest points in the two geographies.
ST_GEOGFROMTEXT(well_known_text)Converts a well known text to a geography.
ST_GEOGPOINT(longitude, latitude)Constructs a new point with the given longitude and latitude.
ST_INTERSECTS(geography_a, geography_b)Returns true if and only if geography_a has some overlap with geography_b.
ST_VALIDATE_GEOGRAPHY(geo_object)Given a geographic object, validates the structure for usage in queries. If the parameter is valid, null is returned. Otherwise, a string is returned with an error message.
ST_X(point)Given a geographical point, returns its longitude. If the parameter is a linestring or polygon, and error is thrown.
ST_Y(point)Given a geographical point, returns its latitude. If the parameter is a linestring or polygon, and error is thrown.
SUBSTR(string, start[, length])Returns substring of string starting at character at index given by start (1-based index) and of length length. If length is not given, returns the substring starting at start until the end of string. If start is negative, it rolls over to the end of the string and counts backwards from there.
SUFFIXES(string, truncate_length)Returns an array of all suffixes of string where each returned suffix is truncated to truncate_length. If not specified, the default truncate_length used is 100. The max length allowed for the input string is 100,000.
SUM(x)Returns the sum of all elements in x. Returns a value of type int if all of the input elements are int, float otherwise.
ATAN(x)Computes the arc tangent of x.
ATANH(x)Computes the inverse hyperbolic tangent of x.
CURRENT_TIME([timezone])Returns current time in the timezone time zone (default UTC). Return value is of time type.
TIMESTAMP_MICROS(n)Constructs value of type timestamp from n microseconds since the Unix epoch.
TIMESTAMP_MILLIS(n)Constructs value of type timestamp from n milliseconds since the Unix epoch.
TIMESTAMP_SECONDS(n)Constructs value of type timestamp from n seconds since the Unix epoch.
TIME_BUCKET(interval, timestamp[, origin])Truncates timestamp to the largest multiple of interval smaller than or equal to timestamp.
TOKENIZE(text[, locale])Tokenize text into array of strings, normalized and lower-cased. text is interpreted as text in the language specified by locale. locale is specified using the ICU format. The default is en_US_POSIX. This function is usually used in an ingest transformation to convert a text field into array of strings at the time of document ingestion. Once an input text field is converted to array of strings, you can use SEARCH function in a SQL query to perform text search using this array of strings.
TO_BASE64(b)Encodes the bytes value b into a base64 string representation.
TO_HEX(b)Encodes the bytes value b into a hex string representation.
TO_UTF8(s)Return the bytes UTF-8 representation of the string value s.
LTRIM(string [, characters])Remove the longest string containing only characters from characters (a space by default) from the start of string
TRUNC(x)Alias of TRUNCATE.
TRUNCATE(x)Rounds x toward zero, returning the nearest integral value that is not larger in magnitude than x.
TRYReturns the result of evaluating expression. If an error is encountered while evaluating expression, returns null instead.
TRY_CAST(x AS type)Same as CAST, except it doesn't error out the query for an unsupported cast; a null is returned instead.
TRY_STATIC_CAST(x AS type)Same as STATIC_CAST, except it doesn't error out the query for an unsupported cast; a null is returned instead.
TYPEOF(x)Returns a string, name of the type of x.
UNIX_DATE(date)Given a date, returns an integer representing the number of days since January 1st, 1970.
UNIX_MICROS(ts)Returns the value of the timestamp ts as an int number of microseconds since the Unix epoch.
UNIX_MILLIS(ts)Returns the value of the timestamp ts as an int number of milliseconds since the Unix epoch.
UNIX_SECONDS(ts)Returns the value of the timestamp ts as an int number of seconds since the Unix epoch.
UPPER(x)Returns locale-independent uppercase string x.
URL_ENCODE(value)Encodes the string into a percent-encoded ASCII text representation
URL_ENCODE(value)Encodes the string into a percent-encoded ASCII text representation
VALUES(obj)Return an array containing the values of obj. The order is unspecified, but will be the same between KEYS(), VALUES(), and ITEMS().
VECTOR_ADD(array, addend)Adds a scalar of type int or float to a vector. Alternatively, adds two vectors.
VECTOR_DIVIDE(array, divisor)Divides a vector by a scalar of type int or float or computes the element-wise divison of two vectors. Throws an error when division by zero occurs.
VECTOR_ENFORCE(array, length, type)Returns the input vector if it fulfills the length and type requirements. Otherwise returns null. Input the length argument as an int and the type argument as the string 'int' or 'float'.
VECTOR_MULTIPLY(array, multiplier)Multiplies a vector by a scalar of type int or float or computes the element-wise multiplication of two vectors as the Hadamard product.
VECTOR_SUBTRACT(array, subtrahend)Subtracts a scalar of type int or float from a vector. Alternatively, subtracts a vector from another vector.
XXH3_64(x[, seed])Computes the 64 bit XXH3 hash of x using seed as the seed. seed is optional, default value is 0.
YEARS(n)Constructs a month_interval value that refers to a duration of n years.
ZIP(entries)Construct an object from an array of entries. Each entry in entry must itself be an array of size 2: the first element is the key (and must be a string), and the second element is the value.