Functions (alphabetical)

This topic contains all functions supported by GoogleSQL for Bigtable.

Function list

Name Summary
ABS Computes the absolute value of X.
ACOS Computes the inverse cosine of X.
ACOSH Computes the inverse hyperbolic cosine of X.
ANY_VALUE Gets an expression for some row.
APPROX_COUNT_DISTINCT Gets the approximate result for COUNT(DISTINCT expression).
APPROX_QUANTILES Gets the approximate quantile boundaries.
APPROX_TOP_COUNT Gets the approximate top elements and their approximate count.
APPROX_TOP_SUM Gets the approximate top elements and sum, based on the approximate sum of an assigned weight.
ARRAY_AGG Gets an array of values.
ARRAY_CONCAT Concatenates one or more arrays with the same element type into a single array.
ARRAY_CONCAT_AGG Concatenates arrays and returns a single array as a result.
ARRAY_FILTER Takes an array, filters out unwanted elements, and returns the results in a new array.
ARRAY_FIRST Gets the first element in an array.
ARRAY_INCLUDES Checks if there is an element in the array that is equal to a search value.
ARRAY_INCLUDES_ALL Checks if all search values are in an array.
ARRAY_INCLUDES_ANY Checks if any search values are in an array.
ARRAY_IS_DISTINCT Checks if an array contains no repeated elements.
ARRAY_LAST Gets the last element in an array.
ARRAY_LAST_N Gets the suffix of an array, consisting of the last n elements.
ARRAY_LENGTH Gets the number of elements in an array.
ARRAY_OFFSET Searches an array from the beginning or ending and produces the zero-based offset for the first matching element.
ARRAY_OFFSETS Searches an array and gets the zero-based offsets for matching elements.
ARRAY_REVERSE Reverses the order of elements in an array.
ARRAY_SLICE Produces an array containing zero or more consecutive elements from an input array.
ARRAY_TO_STRING Produces a concatenation of the elements in an array as a STRING value.
ARRAY_TRANSFORM Transforms the elements of an array, and returns the results in a new array.
ASCII Gets the ASCII code for the first character or byte in a STRING or BYTES value.
ASIN Computes the inverse sine of X.
ASINH Computes the inverse hyperbolic sine of X.
ATAN Computes the inverse tangent of X.
ATAN2 Computes the inverse tangent of X/Y, using the signs of X and Y to determine the quadrant.
ATANH Computes the inverse hyperbolic tangent of X.
AVG Gets the average of non-NULL values.
BIT_AND Performs a bitwise AND operation on an expression.
BIT_OR Performs a bitwise OR operation on an expression.
BIT_XOR Performs a bitwise XOR operation on an expression.
BYTE_LENGTH Gets the number of BYTES in a STRING or BYTES value.
CAST Convert the results of an expression to the given type.
CEIL Gets the smallest integral value that isn't less than X.
CEILING Synonym of CEIL.
CHAR_LENGTH Gets the number of characters in a STRING value.
CHR Converts a Unicode code point to a character.
CODE_POINTS_TO_BYTES Converts an array of extended ASCII code points to a BYTES value.
CODE_POINTS_TO_STRING Converts an array of extended ASCII code points to a STRING value.
CONCAT Concatenates one or more STRING or BYTES values into a single result.
CORR Computes the Pearson coefficient of correlation of a set of number pairs.
COS Computes the cosine of X.
COSH Computes the hyperbolic cosine of X.
COSINE_DISTANCE Computes the cosine distance between two vectors.
COT Computes the cotangent of X.
COTH Computes the hyperbolic cotangent of X.
COUNT Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other than NULL.
COUNTIF Gets the number of TRUE values for an expression.
COVAR_POP Computes the population covariance of a set of number pairs.
COVAR_SAMP Computes the sample covariance of a set of number pairs.
CSC Computes the cosecant of X.
CSCH Computes the hyperbolic cosecant of X.
CURRENT_DATE Returns the current date as a DATE value.
CURRENT_TIMESTAMP Returns the current date and time as a TIMESTAMP object.
DATE Constructs a DATE value.
DATE_ADD Adds a specified time interval to a DATE value.
DATE_DIFF Gets the number of unit boundaries between two DATE values at a particular time granularity.
DATE_FROM_UNIX_DATE Interprets an INT64 expression as the number of days since 1970-01-01.
DATE_SUB Subtracts a specified time interval from a DATE value.
DATE_TRUNC Truncates a DATE, DATETIME, or TIMESTAMP value at a particular granularity.
DIV Divides integer X by integer Y.
ENDS_WITH Checks if a STRING or BYTES value is the suffix of another value.
EXP Computes e to the power of X.
EXTRACT Extracts part of a date from a DATE value.
EXTRACT Extracts part of a TIMESTAMP value.
EUCLIDEAN_DISTANCE Computes the Euclidean distance between two vectors.
FLOOR Gets the largest integral value that isn't greater than X.
FORMAT_DATE Formats a DATE value according to a specified format string.
FORMAT_TIMESTAMP Formats a TIMESTAMP value according to the specified format string.
FORMAT Formats data and produces the results as a STRING value.
FROM_BASE32 Converts a base32-encoded STRING value into a BYTES value.
FROM_BASE64 Converts a base64-encoded STRING value into a BYTES value.
FROM_HEX Converts a hexadecimal-encoded STRING value into a BYTES value.
GENERATE_ARRAY Generates an array of values in a range.
GENERATE_DATE_ARRAY Generates an array of dates in a range.
GENERATE_TIMESTAMP_ARRAY Generates an array of timestamps in a range.
GREATEST Gets the greatest value among X1,...,XN.
HLL_COUNT.EXTRACT Extracts a cardinality estimate of an HLL++ sketch.
HLL_COUNT.INIT Aggregates values of the same underlying type into a new HLL++ sketch.
HLL_COUNT.MERGE Merges HLL++ sketches of the same underlying type into a new sketch, and then gets the cardinality of the new sketch.
HLL_COUNT.MERGE_PARTIAL Merges HLL++ sketches of the same underlying type into a new sketch.
IEEE_DIVIDE Divides X by Y, but doesn't generate errors for division by zero or overflow.
IFERROR Evaluates a try expression, and if an evaluation error is produced, returns the result of a catch expression.
INITCAP Formats a STRING as proper case, which means that the first character in each word is uppercase and all other characters are lowercase.
INSTR Finds the position of a subvalue inside another value, optionally starting the search at a given offset or occurrence.
IS_INF Checks if X is positive or negative infinity.
IS_NAN Checks if X is a NaN value.
ISERROR Evaluates a try expression, and if an evaluation error is produced, returns TRUE.
JSON_EXTRACT (Deprecated) Extracts a JSON value and converts it to a SQL JSON-formatted STRING value.
JSON_EXTRACT_SCALAR (Deprecated) Extracts a JSON scalar value and converts it to a SQL STRING value.
JSON_QUERY Extracts a JSON value and converts it to a SQL JSON-formatted STRING value.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQL ARRAY<JSON-formatted STRING> value.
JSON_VALUE Extracts a JSON scalar value and converts it to a SQL STRING value.
LAST_DAY Gets the last day in a specified time period that contains a DATE value.
LAST_DAY Gets the last day in a specified time period that contains a DATETIME value.
LEAST Gets the least value among X1,...,XN.
LEFT Gets the specified leftmost portion from a STRING or BYTES value.
LENGTH Gets the length of a STRING or BYTES value.
LN Computes the natural logarithm of X.
LOG Computes the natural logarithm of X or the logarithm of X to base Y.
LOG10 Computes the natural logarithm of X to base 10.
LOGICAL_AND Gets the logical AND of all non-NULL expressions.
LOGICAL_OR Gets the logical OR of all non-NULL expressions.
LOWER Formats alphabetic characters in a STRING value as lowercase.

Formats ASCII characters in a BYTES value as lowercase.
LPAD Prepends a STRING or BYTES value with a pattern.
LTRIM Identical to the TRIM function, but only removes leading characters.
MAP_CONTAINS_KEY Checks if a key is in a map.
MAP_EMPTY Checks if a map is empty.
MAP_ENTRIES Gets an array of key-value pairs from a map, sorted in ascending order by key.
MAP_KEYS Gets an array of keys from a map, sorted in ascending order.
MAP_VALUES Gets an array of values from a map, sorted in ascending order by key.
MAX Gets the maximum non-NULL value.
MIN Gets the minimum non-NULL value.
MOD Gets the remainder of the division of X by Y.
NORMALIZE Case-sensitively normalizes the characters in a STRING value.
NORMALIZE_AND_CASEFOLD Case-insensitively normalizes the characters in a STRING value.
NULLIFERROR Evaluates a try expression, and if an evaluation error is produced, returns NULL.
OCTET_LENGTH Alias for BYTE_LENGTH.
PARSE_DATE Converts a STRING value to a DATE value.
PARSE_TIMESTAMP Converts a STRING value to a TIMESTAMP value.
POW Produces the value of X raised to the power of Y.
POWER Synonym of POW.
RAND Generates a pseudo-random value of type FLOAT64 in the range of [0, 1).
REGEXP_CONTAINS Checks if a value is a partial match for a regular expression.
REGEXP_EXTRACT Produces a substring that matches a regular expression.
REGEXP_EXTRACT_ALL Produces an array of all substrings that match a regular expression.
REGEXP_INSTR Finds the position of a regular expression match in a value, optionally starting the search at a given offset or occurrence.
REGEXP_REPLACE Produces a STRING value where all substrings that match a regular expression are replaced with a specified value.
REPEAT Produces a STRING or BYTES value that consists of an original value, repeated.
REPLACE Replaces all occurrences of a pattern with another pattern in a STRING or BYTES value.
REVERSE Reverses a STRING or BYTES value.
RIGHT Gets the specified rightmost portion from a STRING or BYTES value.
ROUND Rounds X to the nearest integer or rounds X to N decimal places after the decimal point.
RPAD Appends a STRING or BYTES value with a pattern.
RTRIM Identical to the TRIM function, but only removes trailing characters.
SAFE_ADD Equivalent to the addition operator (X + Y), but returns NULL if overflow occurs.
SAFE_CAST Similar to the CAST function, but returns NULL when a runtime error is produced.
SAFE_CONVERT_BYTES_TO_STRING Converts a BYTES value to a STRING value and replace any invalid UTF-8 characters with the Unicode replacement character, U+FFFD.
SAFE_DIVIDE Equivalent to the division operator (X / Y), but returns NULL if an error occurs.
SAFE_MULTIPLY Equivalent to the multiplication operator (X * Y), but returns NULL if overflow occurs.
SAFE_NEGATE Equivalent to the unary minus operator (-X), but returns NULL if overflow occurs.
SAFE_SUBTRACT Equivalent to the subtraction operator (X - Y), but returns NULL if overflow occurs.
SEC Computes the secant of X.
SECH Computes the hyperbolic secant of X.
SIGN Produces -1 , 0, or +1 for negative, zero, and positive arguments respectively.
SIN Computes the sine of X.
SINH Computes the hyperbolic sine of X.
SOUNDEX Gets the Soundex codes for words in a STRING value.
SPLIT Splits a STRING or BYTES value, using a delimiter.
SQRT Computes the square root of X.
STARTS_WITH Checks if a STRING or BYTES value is a prefix of another value.
STDDEV An alias of the STDDEV_SAMP function.
STDDEV_POP Computes the population (biased) standard deviation of the values.
STDDEV_SAMP Computes the sample (unbiased) standard deviation of the values.
STRING (Timestamp) Converts a TIMESTAMP value to a STRING value.
STRING_AGG Concatenates non-NULL STRING or BYTES values.
STRPOS Finds the position of the first occurrence of a subvalue inside another value.
SUBSTR Gets a portion of a STRING or BYTES value.
SUBSTRING Alias for SUBSTR
SUM Gets the sum of non-NULL values.
TAN Computes the tangent of X.
TANH Computes the hyperbolic tangent of X.
Temporal filters Access temporal elements of a table by using the Bigtable table name as a function.
TIMESTAMP Constructs a TIMESTAMP value.
TIMESTAMP_ADD Adds a specified time interval to a TIMESTAMP value.
TIMESTAMP_DIFF Gets the number of unit boundaries between two TIMESTAMP values at a particular time granularity.
TIMESTAMP_FROM_UNIX_MICROS Similar to TIMESTAMP_MICROS, except that additionally, a TIMESTAMP value can be passed in.
TIMESTAMP_FROM_UNIX_MILLIS Similar to TIMESTAMP_MILLIS, except that additionally, a TIMESTAMP value can be passed in.
TIMESTAMP_FROM_UNIX_SECONDS Similar to TIMESTAMP_SECONDS, except that additionally, a TIMESTAMP value can be passed in.
TIMESTAMP_MICROS Converts the number of microseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP.
TIMESTAMP_MILLIS Converts the number of milliseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP.
TIMESTAMP_SECONDS Converts the number of seconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP.
TIMESTAMP_SUB Subtracts a specified time interval from a TIMESTAMP value.
TIMESTAMP_TRUNC Truncates a TIMESTAMP or DATETIME value at a particular granularity.
TO_BASE32 Converts a BYTES value to a base32-encoded STRING value.
TO_BASE64 Converts a BYTES value to a base64-encoded STRING value.
TO_CODE_POINTS Converts a STRING or BYTES value into an array of extended ASCII code points.
TO_FLOAT32 Converts the big-endian bytes of a 32-bit IEEE 754 floating point number into a FLOAT32 value.
TO_FLOAT64 Converts the big-endian bytes of a 64-bit IEEE 754 floating point number into a FLOAT64 value.
TO_HEX Converts a BYTES value to a hexadecimal STRING value.
TO_INT64 Converts the big-endian bytes of a 64-bit signed integer into an INT64 value.
TO_JSON_STRING Converts a SQL value to a JSON-formatted STRING value.
TO_VECTOR32 Converts the big-endian bytes of one or more 32-bit IEEE 754 floating point numbers into an ARRAY<FLOAT32> value.
TO_VECTOR64 Converts the big-endian bytes of one or more 64-bit IEEE 754 floating point numbers into an ARRAY<FLOAT64> value.
TRANSLATE Within a value, replaces each source character with the corresponding target character.
TRIM Removes the specified leading and trailing Unicode code points or bytes from a STRING or BYTES value.
TRUNC Rounds a number like ROUND(X) or ROUND(X, N), but always rounds towards zero and never overflows.
UNICODE Gets the Unicode code point for the first character in a value.
UNIX_DATE Converts a DATE value to the number of days since 1970-01-01.
UNIX_MICROS Converts a TIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC.
UNIX_MILLIS Converts a TIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC.
UNIX_SECONDS Converts a TIMESTAMP value to the number of seconds since 1970-01-01 00:00:00 UTC.
UNPACK Expands the timestamped values in an input row into multiple rows, each row representing a different timestamp; and moves the timestamps into a `_timestamp` column.
UPPER Formats alphabetic characters in a STRING value as uppercase.

Formats ASCII characters in a BYTES value as uppercase.
VAR_POP Computes the population (biased) variance of the values.
VAR_SAMP Computes the sample (unbiased) variance of the values.
VARIANCE An alias of VAR_SAMP.