Functions (alphabetical) in GoogleSQL

This topic contains all functions supported by GoogleSQL for Spanner.

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` .
`ADDDATE`	Alias for `DATE_ADD` .
`ANY_VALUE`	Gets an expression for some row.
`APPROX_COSINE_DISTANCE`	Computes the approximate cosine distance between two vectors.
`APPROX_DOT_PRODUCT`	Computes the approximate dot product of two vectors.
`APPROX_EUCLIDEAN_DISTANCE`	Computes the approximate Euclidean distance between two vectors.
`ARRAY`	Produces an array with one element for each row in a subquery.
`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_LENGTH`	Gets the number of elements in an array.
`ARRAY_MAX`	Gets the maximum non- `NULL` value in an array.
`ARRAY_MIN`	Gets the minimum non- `NULL` value in an array.
`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.
`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_COUNT`	Gets the number of bits that are set in an input expression.
`BIT_OR`	Performs a bitwise OR operation on an expression.
`BIT_REVERSE`	Reverses the bits in an integer.
`BIT_XOR`	Performs a bitwise XOR operation on an expression.
`BOOL`	Converts a JSON boolean to a SQL `BOOL` value.
`BOOL_ARRAY`	Converts a JSON array of booleans to a SQL `ARRAY<BOOL>` value.
`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.
`CHARACTER_LENGTH`	Synonym for `CHAR_LENGTH` .
`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.
`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.
`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` value at a particular granularity.
`DEBUG_TOKENLIST`	Displays a human-readable representation of tokens present in the `TOKENLIST` value for debugging purposes.
`DESTINATION_NODE_ID`	Gets a unique identifier of a graph edge's destination node.
`DIV`	Divides integer `X` by integer `Y` .
`DOT_PRODUCT`	Computes the dot product of two vectors.
`FLOAT64`	Converts a JSON number to a SQL `FLOAT64` value.
`FLOAT64_ARRAY`	Converts a JSON array of numbers to a SQL `ARRAY<FLOAT64>` value.
`EDGES`	Gets the edges in a graph path. The resulting array retains the original order in the graph path.
`ELEMENT_ID`	Gets a graph element's unique identifier.
`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.
`FLOAT32`	Converts a JSON number to a SQL `FLOAT32` value.
`FLOAT32_ARRAY`	Converts a JSON array of numbers to a SQL `ARRAY<FLOAT32>` value.
`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_UUID`	Produces a random universally unique identifier (UUID) as a `STRING` value.
`GET_INTERNAL_SEQUENCE_STATE`	Gets the current sequence internal counter before bit reversal.
`GET_NEXT_SEQUENCE_VALUE`	Takes in a sequence identifier and returns the next value. This function is only allowed in read-write transactions.
`GREATEST`	Gets the greatest value among `X1,...,XN` .
`IEEE_DIVIDE`	Divides `X` by `Y` , but doesn't generate errors for division by zero or overflow.
`INT64`	Converts a JSON number to a SQL `INT64` value.
`INT64_ARRAY`	Converts a JSON array of numbers to a SQL `ARRAY<INT64>` value.
`IS_ACYCLIC`	Checks if a graph path has a repeating node.
`IS_FIRST`	Returns `true` if this row is in the first `k` rows (1-based) within the window.
`IS_INF`	Checks if `X` is positive or negative infinity.
`IS_NAN`	Checks if `X` is a `NaN` value.
`IS_SIMPLE`	Checks if a graph path is simple.
`IS_TRAIL`	Checks if a graph path has a repeating edge.
`JSON_ARRAY`	Creates a JSON array.
`JSON_ARRAY_APPEND`	Appends JSON data to the end of a JSON array.
`JSON_ARRAY_INSERT`	Inserts JSON data into a JSON array.
`JSON_CONTAINS`	Checks if a JSON document contains another JSON document.
`JSON_KEYS`	Extracts unique JSON keys from a JSON expression.
`JSON_OBJECT`	Creates a JSON object.
`JSON_QUERY`	Extracts a JSON value and converts it to a SQL JSON-formatted `STRING` or `JSON` value.
`JSON_QUERY_ARRAY`	Extracts a JSON array and converts it to a SQL `ARRAY<JSON-formatted STRING>` or `ARRAY<JSON>` value.
`JSON_REMOVE`	Produces JSON with the specified JSON data removed.
`JSON_SET`	Inserts or replaces JSON data.
`JSON_STRIP_NULLS`	Removes JSON nulls from JSON objects and JSON arrays.
`JSON_TYPE`	Gets the JSON type of the outermost JSON value and converts the name of this type to a SQL `STRING` value.
`JSON_VALUE`	Extracts a JSON scalar value and converts it to a SQL `STRING` value.
`JSON_VALUE_ARRAY`	Extracts a JSON array of scalar values and converts it to a SQL `ARRAY<STRING>` value.
`JUSTIFY_DAYS`	Normalizes the day part of an `INTERVAL` value.
`JUSTIFY_HOURS`	Normalizes the time part of an `INTERVAL` value.
`JUSTIFY_INTERVAL`	Normalizes the day and time parts of an `INTERVAL` value.
`LABELS`	Gets the labels associated with a graph element.
`LAX_BOOL`	Attempts to convert a JSON value to a SQL `BOOL` value.
`LAX_FLOAT64`	Attempts to convert a JSON value to a SQL `FLOAT64` value.
`LAX_INT64`	Attempts to convert a JSON value to a SQL `INT64` value.
`LAX_STRING`	Attempts to convert a JSON value to a SQL `STRING` value.
`LCASE`	Alias for `LOWER` .
`LEAST`	Gets the least value among `X1,...,XN` .
`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.
`MAX`	Gets the maximum non- `NULL` value.
`MIN`	Gets the minimum non- `NULL` value.
`ML.PREDICT`	Apply ML computations defined by a model to each row of an input relation.
`MOD`	Gets the remainder of the division of `X` by `Y` .
`NET.HOST`	Gets the hostname from a URL.
`NET.IP_FROM_STRING`	Converts an IPv4 or IPv6 address from a `STRING` value to a `BYTES` value in network byte order.
`NET.IP_NET_MASK`	Gets a network mask.
`NET.IP_TO_STRING`	Converts an IPv4 or IPv6 address from a `BYTES` value in network byte order to a `STRING` value.
`NET.IP_TRUNC`	Converts a `BYTES` IPv4 or IPv6 address in network byte order to a `BYTES` subnet address.
`NET.IPV4_FROM_INT64`	Converts an IPv4 address from an `INT64` value to a `BYTES` value in network byte order.
`NET.IPV4_TO_INT64`	Converts an IPv4 address from a `BYTES` value in network byte order to an `INT64` value.
`NET.PUBLIC_SUFFIX`	Gets the public suffix from a URL.
`NET.REG_DOMAIN`	Gets the registered or registrable domain from a URL.
`NET.SAFE_IP_FROM_STRING`	Similar to the `NET.IP_FROM_STRING` , but returns `NULL` instead of producing an error if the input is invalid.
`NODES`	Gets the nodes in a graph path. The resulting array retains the original order in the graph path.
`NORMALIZE`	Case-sensitively normalizes the characters in a `STRING` value.
`NORMALIZE_AND_CASEFOLD`	Case-insensitively normalizes the characters in a `STRING` value.
`OCTET_LENGTH`	Alias for `BYTE_LENGTH` .
`PARSE_DATE`	Converts a `STRING` value to a `DATE` value.
`PARSE_JSON`	Converts a JSON-formatted `STRING` value to a `JSON` value.
`PARSE_TIMESTAMP`	Converts a `STRING` value to a `TIMESTAMP` value.
`PATH`	Creates a graph path from a list of graph elements.
`PATH_FIRST`	Gets the first node in a graph path.
`PATH_LAST`	Gets the last node in a graph path.
`PATH_LENGTH`	Gets the number of edges in a graph path.
`PENDING_COMMIT_TIMESTAMP`	Write a pending commit timestamp.
`POW`	Produces the value of `X` raised to the power of `Y` .
`POWER`	Synonym of `POW` .
`PROPERTY_NAMES`	Gets the property names associated with a graph element.
`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_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.
`REPLACE_FIELDS`	Replaces the values in one or more protocol buffer fields.
`REVERSE`	Reverses 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.
`SAFE_TO_JSON`	Similar to the `TO_JSON` function, but for each unsupported field in the input argument, produces a JSON null instead of an error.
`SCORE`	Calculates a relevance score of a `TOKENLIST` for a full-text search query. The higher the score, the stronger the match.
`SCORE_NGRAMS`	Calculates a relevance score of a `TOKENLIST` for a fuzzy search. The higher the score, the stronger the match.
`SEARCH`	Returns `TRUE` if a full-text search query matches tokens.
`SEARCH_NGRAMS`	Checks whether enough n-grams match the tokens in a fuzzy search.
`SEARCH_SUBSTRING`	Returns `TRUE` if a substring query matches tokens.
`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` .
`SNIPPET`	Gets a list of snippets that match a full-text search query.
`SOURCE_NODE_ID`	Gets a unique identifier of a graph edge's source node.
`SOUNDEX`	Gets the Soundex codes for words in a `STRING` value.
`SPLIT`	Splits a `STRING` or `BYTES` value, using a delimiter.
`SPLIT_SUBSTR`	Returns the substring from an input string that's determined by a delimiter, a location that indicates the first split of the substring to return, and the number of splits to include.
`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_SAMP`	Computes the sample (unbiased) standard deviation of the values.
`STRING` (JSON)	Converts a JSON string to a SQL `STRING` value.
`STRING_ARRAY`	Converts a JSON array of strings to a SQL `ARRAY<STRING>` value.
`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.
`SUBDATE`	Alias for `DATE_SUB` .
`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` .
`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_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` 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_JSON`	Converts a SQL value to a JSON value.
`TO_JSON_STRING`	Converts a `JSON` value to a SQL JSON-formatted `STRING` value.
`TOKEN`	Constructs an exact match `TOKENLIST` value by tokenizing a `BYTE` or `STRING` value verbatim to accelerate exact match expressions in SQL.
`TOKENIZE_BOOL`	Constructs a boolean `TOKENLIST` value by tokenizing a `BOOL` value to accelerate boolean match expressions in SQL.
`TOKENIZE_FULLTEXT`	Constructs a full-text `TOKENLIST` value by tokenizing text for full-text matching.
`TOKENIZE_JSON`	Constructs a JSON `TOKENLIST` value by tokenizing a `JSON` value to accelerate JSON predicate expressions in SQL.
`TOKENIZE_NGRAMS`	Constructs an n-gram `TOKENLIST` value by tokenizing a `STRING` value for matching n-grams.
`TOKENIZE_NUMBER`	Constructs a numeric `TOKENLIST` value by tokenizing numeric values to accelerate numeric comparison expressions in SQL.
`TOKENIZE_SUBSTRING`	Constructs a substring `TOKENLIST` value by tokenizing text for substring matching.
`TOKENLIST_CONCAT`	Constructs a `TOKENLIST` value by concatenating one or more `TOKENLIST` values.
`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.
`UCASE`	Alias for `UPPER` .
`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.
`VAR_SAMP`	Computes the sample (unbiased) variance of the values.
`VARIANCE`	An alias of `VAR_SAMP` .

Functions (alphabetical) in GoogleSQL Stay organized with collections Save and categorize content based on your preferences.

Function list

Functions (alphabetical) in GoogleSQL