All Functions

All Functions (Alphabetical)

This page lists all Rockset SQL functions in alphabetical order.

Function	Description
`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`.
`CASE`	`CASE` 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 `group`th 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(xx + yy)`, 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 FROM`	Returns `true` if `x` is not equal to `y`, and `false` otherwise.
`IS NULL`	Returns `true` if `x` is `null` or `undefined`.
`IS UNDEFINED`	Returns `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 `group`th 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 `group`th 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`.
`TRY`	Returns 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.