Functions (alphabetical) in GoogleSQL

GoogleSQL is the new name for Google Standard SQL! New name, same great SQL dialect.

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.