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.
ARRAY_CONCAT	Concatenates one or more arrays with the same element type into a single array.
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.
AVG (Differential Privacy)	DIFFERENTIAL_PRIVACY-supported AVG. Gets the differentially-private average of non-NULL, non-NaN values in a query with a DIFFERENTIAL_PRIVACY clause.
BIT_AND	Performs a bitwise AND operation on an expression.
BIT_CAST_TO_INT64	Cast bits to an INT64 value.
BIT_COUNT	Gets the number of bits that are set in an input 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 is not 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.
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.
COUNT (Differential Privacy)	DIFFERENTIAL_PRIVACY-supported COUNT. Signature 1: Gets the differentially-private count of rows in a query with a DIFFERENTIAL_PRIVACY clause. Signature 2: Gets the differentially-private count of rows with a non-NULL expression in a query with a DIFFERENTIAL_PRIVACY clause.
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_BUCKET	Gets the lower bound of the date bucket that contains a date.
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.
DATETIME_BUCKET	Gets the lower bound of the datetime bucket that contains a datetime.
DETERMINISTIC_DECRYPT_BYTES	Uses the matching key from a keyset to decrypt a BYTES ciphertext, using deterministic AEAD.
DETERMINISTIC_DECRYPT_STRING	Uses the matching key from a keyset to decrypt a BYTES ciphertext into a STRING plaintext, using deterministic AEAD.
DETERMINISTIC_ENCRYPT	Encrypts STRING plaintext, using the primary cryptographic key in a keyset, using deterministic AEAD encryption.
DIV	Divides integer X by integer Y.
ENDS_WITH	Checks if a STRING or BYTES value is the suffix of another value.
ERROR	Produces an error with a custom error message.
EXP	Computes e to the power of X.
EXTRACT	Extracts part of a date from a DATE value.
EXTRACT	Extracts part of an INTERVAL value.
EXTRACT	Extracts part of a TIMESTAMP value.
EUCLIDEAN_DISTANCE	Computes the Euclidean distance between two vectors.
FARM_FINGERPRINT	Computes the fingerprint of a STRING or BYTES value, using the FarmHash Fingerprint64 algorithm.
FLOOR	Gets the largest integral value that is not 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 does not 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.
MAKE_INTERVAL	Constructs an INTERVAL value.
MAP_CONTAINS_KEY	Checks if a key is in a map.
MAP_EMPTY	Checks if a map is empty.
MAP_KEYS	Gets an array of lexicographically sorted keys from a map.
MAP_VALUES	Gets an array of lexicographically key-sorted values from a map.
MD5	Computes the hash of a STRING or BYTES value, using the MD5 algorithm.
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.
PERCENTILE_CONT (Differential Privacy)	DIFFERENTIAL_PRIVACY-supported PERCENTILE_CONT. Computes a differentially-private percentile across privacy unit columns in a query with a DIFFERENTIAL_PRIVACY clause.
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).
RANGE	Constructs a range of DATE, DATETIME, or TIMESTAMP values.
RANGE_CONTAINS	Signature 1: Checks if one range is in another range. Signature 2: Checks if a value is in a range.
RANGE_END	Gets the upper bound of a range.
RANGE_OVERLAPS	Checks if two ranges overlap.
RANGE_SESSIONIZE	Produces a table of sessionized ranges.
RANGE_START	Gets the lower bound of a range.
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.
S2_CELLIDFROMPOINT	Gets the S2 cell ID covering a point GEOGRAPHY value.
S2_COVERINGCELLIDS	Gets an array of S2 cell IDs that cover a GEOGRAPHY value.
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.
SHA1	Computes the hash of a STRING or BYTES value, using the SHA-1 algorithm.
SHA256	Computes the hash of a STRING or BYTES value, using the SHA-256 algorithm.
SHA512	Computes the hash of a STRING or BYTES value, using the SHA-512 algorithm.
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.
STRING (Timestamp)	Converts a TIMESTAMP value to a STRING value.
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.
SUM (Differential Privacy)	DIFFERENTIAL_PRIVACY-supported SUM. Gets the differentially-private sum of non-NULL, non-NaN values in a query with a DIFFERENTIAL_PRIVACY clause.
TAN	Computes the tangent of X.
TANH	Computes the hyperbolic tangent of X.
TIMESTAMP	Constructs a TIMESTAMP value.
TIMESTAMP_ADD	Adds a specified time interval to a TIMESTAMP value.
TIMESTAMP_BUCKET	Gets the lower bound of the timestamp bucket that contains a timestamp.
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_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.
UPPER	Formats alphabetic characters in a STRING value as uppercase. Formats ASCII characters in a BYTES value as uppercase.