Functions (alphabetical) in GoogleSQL

This topic contains all functions supported by GoogleSQL for Spanner.

Function list

NameSummary
ABS Computes the absolute value ofX.
ACOS Computes the inverse cosine ofX.
ACOSH Computes the inverse hyperbolic cosine ofX.
ADDDATE Alias forDATE_ADD.
ANY_VALUE Gets an expression for some row.
APPROX_COSINE_DISTANCEComputes the approximate cosine distance between two vectors.
APPROX_DOT_PRODUCT Computes the approximate dot product of two vectors.
APPROX_EUCLIDEAN_DISTANCEComputes 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 aSTRING value.
ARRAY_TRANSFORM Transforms the elements of an array, and returns the results in a new array.
ASIN Computes the inverse sine ofX.
ASINH Computes the inverse hyperbolic sine ofX.
ATAN Computes the inverse tangent ofX.
ATAN2 Computes the inverse tangent ofX/Y, using the signs ofX andY to determine the quadrant.
ATANH Computes the inverse hyperbolic tangent ofX.
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_REVERSEReverses the bits in an integer.
BIT_XOR Performs a bitwise XOR operation on an expression.
BOOL Converts a JSON boolean to a SQLBOOL value.
BOOL_ARRAY Converts a JSON array of booleans to a SQLARRAY<BOOL> value.
BYTE_LENGTH Gets the number ofBYTES in aSTRING orBYTES value.
CAST Convert the results of an expression to the given type.
CEIL Gets the smallest integral value that isn't less thanX.
CEILING Synonym ofCEIL.
CHAR_LENGTH Gets the number of characters in aSTRING value.
CHARACTER_LENGTH Synonym forCHAR_LENGTH.
CODE_POINTS_TO_BYTES Converts an array of extended ASCII code points to aBYTES value.
CODE_POINTS_TO_STRING Converts an array of extended ASCII code points to aSTRING value.
CONCAT Concatenates one or moreSTRING orBYTES values into a single result.
COS Computes the cosine ofX.
COSH Computes the hyperbolic cosine ofX.
COSINE_DISTANCEComputes 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 thanNULL.
COUNTIF Gets the number ofTRUE values for an expression.
CURRENT_DATE Returns the current date as aDATE value.
CURRENT_TIMESTAMP Returns the current date and time as aTIMESTAMP object.
DATE Constructs aDATE value.
DATE_ADD Adds a specified time interval to aDATE value.
DATE_DIFF Gets the number of unit boundaries between twoDATE values at a particular time granularity.
DATE_FROM_UNIX_DATE Interprets anINT64 expression as the number of days since 1970-01-01.
DATE_SUB Subtracts a specified time interval from aDATE value.
DATE_TRUNC Truncates aDATE value at a particular granularity.
DEBUG_TOKENLISTDisplays a human-readable representation of tokens present in theTOKENLIST value for debugging purposes.
DESTINATION_NODE_IDGets a unique identifier of a graph edge's destination node.
DIV Divides integerX by integerY.
DOT_PRODUCTComputes the dot product of two vectors.
FLOAT64 Converts a JSON number to a SQLFLOAT64 value.
FLOAT64_ARRAY Converts a JSON array of numbers to a SQLARRAY<FLOAT64> value.
EDGES Gets the edges in a graph path. The resulting array retains the original order in the graph path.
ELEMENT_IDGets a graph element's unique identifier.
ENDS_WITH Checks if aSTRING orBYTES value is the suffix of another value.
ERROR Produces an error with a custom error message.
EXP Computese to the power ofX.
EXTRACT Extracts part of a date from aDATE value.
EXTRACT Extracts part of anINTERVAL value.
EXTRACT Extracts part of aTIMESTAMP value.
EUCLIDEAN_DISTANCEComputes the Euclidean distance between two vectors.
FARM_FINGERPRINT Computes the fingerprint of aSTRING orBYTES value, using the FarmHash Fingerprint64 algorithm.
FLOAT32 Converts a JSON number to a SQLFLOAT32 value.
FLOAT32_ARRAYConverts a JSON array of numbers to a SQLARRAY<FLOAT32> value.
FLOOR Gets the largest integral value that isn't greater thanX.
FORMAT_DATE Formats aDATE value according to a specified format string.
FORMAT_TIMESTAMP Formats aTIMESTAMP value according to the specified format string.
FORMAT Formats data and produces the results as aSTRING value.
FROM_BASE32 Converts a base32-encodedSTRING value into aBYTES value.
FROM_BASE64 Converts a base64-encodedSTRING value into aBYTES value.
FROM_HEX Converts a hexadecimal-encodedSTRING value into aBYTES 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 aSTRING value.
GET_INTERNAL_SEQUENCE_STATEGets the current sequence internal counter before bit reversal.
GET_NEXT_SEQUENCE_VALUETakes in a sequence identifier and returns the next value. This function is only allowed in read-write transactions.
GREATEST Gets the greatest value amongX1,...,XN.
IEEE_DIVIDE DividesX byY, but doesn't generate errors for division by zero or overflow.
INT64 Converts a JSON number to a SQLINT64 value.
INT64_ARRAY Converts a JSON array of numbers to a SQLARRAY<INT64> value.
IS_ACYCLICChecks if a graph path has a repeating node.
IS_FIRST Returnstrue if this row is in the firstk rows (1-based) within the window.
IS_INF Checks ifX is positive or negative infinity.
IS_NAN Checks ifX is aNaN value.
IS_SIMPLEChecks if a graph path is simple.
IS_TRAILChecks 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_KEYSExtracts unique JSON keys from a JSON expression.
JSON_OBJECTCreates a JSON object.
JSON_QUERY Extracts a JSON value and converts it to a SQL JSON-formattedSTRING orJSON value.
JSON_QUERY_ARRAY Extracts a JSON array and converts it to a SQLARRAY<JSON-formatted STRING> orARRAY<JSON> value.
JSON_REMOVEProduces JSON with the specified JSON data removed.
JSON_SETInserts or replaces JSON data.
JSON_STRIP_NULLSRemoves 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 SQLSTRING value.
JSON_VALUE Extracts a JSON scalar value and converts it to a SQLSTRING value.
JSON_VALUE_ARRAY Extracts a JSON array of scalar values and converts it to a SQLARRAY<STRING> value.
JUSTIFY_DAYS Normalizes the day part of anINTERVAL value.
JUSTIFY_HOURS Normalizes the time part of anINTERVAL value.
JUSTIFY_INTERVAL Normalizes the day and time parts of anINTERVAL value.
LABELSGets the labels associated with a graph element.
LAX_BOOL Attempts to convert a JSON value to a SQLBOOL value.
LAX_FLOAT64 Attempts to convert a JSON value to a SQLFLOAT64 value.
LAX_INT64 Attempts to convert a JSON value to a SQLINT64 value.
LAX_STRING Attempts to convert a JSON value to a SQLSTRING value.
LCASE Alias forLOWER.
LEAST Gets the least value amongX1,...,XN.
LENGTH Gets the length of aSTRING orBYTES value.
LN Computes the natural logarithm ofX.
LOG Computes the natural logarithm ofX or the logarithm ofX to baseY.
LOG10 Computes the natural logarithm ofX 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 aSTRING value as lowercase.

Formats ASCII characters in aBYTES value as lowercase.
LPAD Prepends aSTRING orBYTES value with a pattern.
LTRIM Identical to theTRIM function, but only removes leading characters.
MAKE_INTERVAL Constructs anINTERVAL 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 ofX byY.
NET.HOST Gets the hostname from a URL.
NET.IP_FROM_STRING Converts an IPv4 or IPv6 address from aSTRING value to aBYTES value in network byte order.
NET.IP_NET_MASK Gets a network mask.
NET.IP_TO_STRING Converts an IPv4 or IPv6 address from aBYTES value in network byte order to aSTRING value.
NET.IP_TRUNC Converts aBYTES IPv4 or IPv6 address in network byte order to aBYTES subnet address.
NET.IPV4_FROM_INT64 Converts an IPv4 address from anINT64 value to aBYTES value in network byte order.
NET.IPV4_TO_INT64 Converts an IPv4 address from aBYTES value in network byte order to anINT64 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 theNET.IP_FROM_STRING, but returnsNULL 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 aSTRING value.
NORMALIZE_AND_CASEFOLD Case-insensitively normalizes the characters in aSTRING value.
OCTET_LENGTH Alias forBYTE_LENGTH.
PARSE_DATE Converts aSTRING value to aDATE value.
PARSE_JSON Converts a JSON-formattedSTRING value to aJSON value.
PARSE_TIMESTAMP Converts aSTRING value to aTIMESTAMP value.
PATHCreates a graph path from a list of graph elements.
PATH_FIRSTGets the first node in a graph path.
PATH_LASTGets the last node in a graph path.
PATH_LENGTHGets the number of edges in a graph path.
PENDING_COMMIT_TIMESTAMP Write a pending commit timestamp.
POW Produces the value ofX raised to the power ofY.
POWER Synonym ofPOW.
PROPERTY_NAMESGets 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 aSTRING value where all substrings that match a regular expression are replaced with a specified value.
REPEAT Produces aSTRING orBYTES value that consists of an original value, repeated.
REPLACE Replaces all occurrences of a pattern with another pattern in aSTRING orBYTES value.
REPLACE_FIELDS Replaces the values in one or more protocol buffer fields.
REVERSE Reverses aSTRING orBYTES value.
ROUND RoundsX to the nearest integer or roundsX toN decimal places after the decimal point.
RPAD Appends aSTRING orBYTES value with a pattern.
RTRIM Identical to theTRIM function, but only removes trailing characters.
SAFE_ADD Equivalent to the addition operator (X + Y), but returnsNULL if overflow occurs.
SAFE_CAST Similar to theCAST function, but returnsNULL when a runtime error is produced.
SAFE_CONVERT_BYTES_TO_STRING Converts aBYTES value to aSTRING 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 returnsNULL if an error occurs.
SAFE_MULTIPLY Equivalent to the multiplication operator (X * Y), but returnsNULL if overflow occurs.
SAFE_NEGATE Equivalent to the unary minus operator (-X), but returnsNULL if overflow occurs.
SAFE_SUBTRACT Equivalent to the subtraction operator (X - Y), but returnsNULL 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 aTOKENLIST for a full-text search query. The higher the score, the stronger the match.
SCORE_NGRAMS Calculates a relevance score of aTOKENLIST for a fuzzy search. The higher the score, the stronger the match.
SEARCH ReturnsTRUE if a full-text search query matches tokens.
SEARCH_NGRAMSChecks whether enough n-grams match the tokens in a fuzzy search.
SEARCH_SUBSTRINGReturnsTRUE if a substring query matches tokens.
SHA1 Computes the hash of aSTRING orBYTES value, using the SHA-1 algorithm.
SHA256 Computes the hash of aSTRING orBYTES value, using the SHA-256 algorithm.
SHA512 Computes the hash of aSTRING orBYTES value, using the SHA-512 algorithm.
SIGN Produces -1 , 0, or +1 for negative, zero, and positive arguments respectively.
SIN Computes the sine ofX.
SINH Computes the hyperbolic sine ofX.
SNIPPETGets a list of snippets that match a full-text search query.
SOURCE_NODE_IDGets a unique identifier of a graph edge's source node.
SOUNDEX Gets the Soundex codes for words in aSTRING value.
SPLIT Splits aSTRING orBYTES 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 ofX.
STARTS_WITH Checks if aSTRING orBYTES value is a prefix of another value.
STDDEV An alias of theSTDDEV_SAMP function.
STDDEV_SAMP Computes the sample (unbiased) standard deviation of the values.
STRING (JSON) Converts a JSON string to a SQLSTRING value.
STRING_ARRAY Converts a JSON array of strings to a SQLARRAY<STRING> value.
STRING (Timestamp) Converts aTIMESTAMP value to aSTRING value.
STRING_AGG Concatenates non-NULLSTRING orBYTES values.
STRPOS Finds the position of the first occurrence of a subvalue inside another value.
SUBDATE Alias forDATE_SUB.
SUBSTR Gets a portion of aSTRING orBYTES value.
SUBSTRINGAlias forSUBSTR
SUM Gets the sum of non-NULL values.
TAN Computes the tangent ofX.
TANH Computes the hyperbolic tangent ofX.
TIMESTAMP Constructs aTIMESTAMP value.
TIMESTAMP_ADD Adds a specified time interval to aTIMESTAMP value.
TIMESTAMP_DIFF Gets the number of unit boundaries between twoTIMESTAMP values at a particular time granularity.
TIMESTAMP_MICROS Converts the number of microseconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP.
TIMESTAMP_MILLIS Converts the number of milliseconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP.
TIMESTAMP_SECONDS Converts the number of seconds since 1970-01-01 00:00:00 UTC to aTIMESTAMP.
TIMESTAMP_SUB Subtracts a specified time interval from aTIMESTAMP value.
TIMESTAMP_TRUNC Truncates aTIMESTAMP value at a particular granularity.
TO_BASE32 Converts aBYTES value to a base32-encodedSTRING value.
TO_BASE64 Converts aBYTES value to a base64-encodedSTRING value.
TO_CODE_POINTS Converts aSTRING orBYTES value into an array of extended ASCII code points.
TO_HEX Converts aBYTES value to a hexadecimalSTRING value.
TO_JSON Converts a SQL value to a JSON value.
TO_JSON_STRING Converts aJSON value to a SQL JSON-formattedSTRING value.
TOKENConstructs an exact matchTOKENLIST value by tokenizing aBYTE orSTRING value verbatim to accelerate exact match expressions in SQL.
TOKENIZE_BOOLConstructs a booleanTOKENLIST value by tokenizing aBOOL value to accelerate boolean match expressions in SQL.
TOKENIZE_FULLTEXTConstructs a full-textTOKENLIST value by tokenizing text for full-text matching.
TOKENIZE_JSONConstructs a JSONTOKENLIST value by tokenizing aJSON value to accelerate JSON predicate expressions in SQL.
TOKENIZE_NGRAMS Constructs an n-gramTOKENLIST value by tokenizing aSTRING value for matching n-grams.
TOKENIZE_NUMBER Constructs a numericTOKENLIST value by tokenizing numeric values to accelerate numeric comparison expressions in SQL.
TOKENIZE_SUBSTRINGConstructs a substringTOKENLIST value by tokenizing text for substring matching.
TOKENLIST_CONCAT Constructs aTOKENLIST value by concatenating one or moreTOKENLIST values.
TRIM Removes the specified leading and trailing Unicode code points or bytes from aSTRING orBYTES value.
TRUNC Rounds a number likeROUND(X) orROUND(X, N), but always rounds towards zero and never overflows.
UCASE Alias forUPPER.
UNIX_DATE Converts aDATE value to the number of days since 1970-01-01.
UNIX_MICROS Converts aTIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC.
UNIX_MILLIS Converts aTIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC.
UNIX_SECONDS Converts aTIMESTAMP value to the number of seconds since 1970-01-01 00:00:00 UTC.
UPPER Formats alphabetic characters in aSTRING value as uppercase.

Formats ASCII characters in aBYTES value as uppercase.
VAR_SAMP Computes the sample (unbiased) variance of the values.
VARIANCE An alias ofVAR_SAMP.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.