Functions (alphabetical)

This topic contains all functions supported by GoogleSQL for BigQuery.

Function list

NameSummary
ABS Computes the absolute value ofX.
ACOS Computes the inverse cosine ofX.
ACOSH Computes the inverse hyperbolic cosine ofX.
AEAD.DECRYPT_BYTES Uses the matching key from a keyset to decrypt aBYTES ciphertext.
AEAD.DECRYPT_STRING Uses the matching key from a keyset to decrypt aBYTES ciphertext into aSTRING plaintext.
AEAD.ENCRYPT EncryptsSTRING plaintext, using the primary cryptographic key in a keyset.
ANY_VALUE Gets an expression for some row.
APPENDS Returns all rows appended to a table for a given time range.
APPROX_COUNT_DISTINCT Gets the approximate result forCOUNT(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 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_FIRST Gets the first element in an array.
ARRAY_LAST Gets the last element in an array.
ARRAY_LENGTH Gets the number of elements 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.
ASCII Gets the ASCII code for the first character or byte in aSTRING orBYTES value.
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.
AVG (Differential Privacy)DIFFERENTIAL_PRIVACY-supportedAVG.

Gets the differentially-private average of non-NULL, non-NaN values in a query with aDIFFERENTIAL_PRIVACY clause.
BAG_OF_WORDSGets the frequency of each term (token) in a tokenized document.
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_XOR Performs a bitwise XOR operation on an expression.
BOOL Converts a JSON boolean to a SQLBOOL value.
BYTE_LENGTH Gets the number ofBYTES in aSTRING orBYTES value.
CAST Convert the results of an expression to the given type.
CBRT Computes the cube root ofX.
CEIL Gets the smallest integral value that isn't less thanX.
CEILING Synonym ofCEIL.
CHANGES Returns all rows that have changed in a table for a given time range.
CHAR_LENGTH Gets the number of characters in aSTRING value.
CHARACTER_LENGTH Synonym forCHAR_LENGTH.
CHR Converts a Unicode code point to a character.
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.
COLLATE Combines aSTRING value and a collation specification into a collation specification-supportedSTRING value.
CONCAT Concatenates one or moreSTRING orBYTES values into a single result.
CONTAINS_SUBSTR Performs a normalized, case-insensitive search to see if a value exists as a substring in an expression.
CORR Computes the Pearson coefficient of correlation of a set of number pairs.
COS Computes the cosine ofX.
COSH Computes the hyperbolic cosine ofX.
COSINE_DISTANCEComputes the cosine distance between two vectors.
COT Computes the cotangent ofX.
COTH Computes the hyperbolic cotangent ofX.
COUNT Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other thanNULL.
COUNT (Differential Privacy)DIFFERENTIAL_PRIVACY-supportedCOUNT.

Signature 1: Gets the differentially-private count of rows in a query with aDIFFERENTIAL_PRIVACY clause.

Signature 2: Gets the differentially-private count of rows with a non-NULL expression in a query with aDIFFERENTIAL_PRIVACY clause.
COUNTIF Gets the number ofTRUE 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 ofX.
CSCH Computes the hyperbolic cosecant ofX.
CUME_DIST Gets the cumulative distribution (relative position (0,1]) of each row within a window.
CURRENT_DATE Returns the current date as aDATE value.
CURRENT_DATETIME Returns the current date and time as aDATETIME value.
CURRENT_TIME Returns the current time as aTIME 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_BUCKET Gets the lower bound of the date bucket that contains a date.
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,DATETIME, orTIMESTAMP value at a particular granularity.
DATETIME Constructs aDATETIME value.
DATETIME_ADD Adds a specified time interval to aDATETIME value.
DATETIME_BUCKET Gets the lower bound of the datetime bucket that contains a datetime.
DATETIME_DIFF Gets the number of unit boundaries between twoDATETIME values at a particular time granularity.
DATETIME_SUB Subtracts a specified time interval from aDATETIME value.
DATETIME_TRUNC Truncates aDATETIME orTIMESTAMP value at a particular granularity.
DENSE_RANK Gets the dense rank (1-based, no gaps) of each row within a window.
DETERMINISTIC_DECRYPT_BYTES Uses the matching key from a keyset to decrypt aBYTES ciphertext, using deterministic AEAD.
DETERMINISTIC_DECRYPT_STRING Uses the matching key from a keyset to decrypt aBYTES ciphertext into aSTRING plaintext, using deterministic AEAD.
DETERMINISTIC_ENCRYPT EncryptsSTRING plaintext, using the primary cryptographic key in a keyset, using deterministic AEAD encryption.
DIV Divides integerX by integerY.
DLP_DETERMINISTIC_ENCRYPTEncrypts data with a DLP compatible algorithm.
DLP_DETERMINISTIC_DECRYPTDecrypts DLP-encrypted data.
DLP_KEY_CHAINGets a data encryption key that's wrapped by Cloud Key Management Service.
FLOAT64 Converts a JSON number to a SQLFLOAT64 value.
EDIT_DISTANCE Computes the Levenshtein distance between twoSTRING orBYTES values.
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.
EXTERNAL_OBJECT_TRANSFORM Produces an object table with the original columns plus one or more additional columns.
EXTERNAL_QUERY Executes a query on an external database and returns the results as a temporary table.
EXTRACT Extracts part of a date from aDATE value.
EXTRACT Extracts part of a date and time from aDATETIME value.
EXTRACT Extracts part of anINTERVAL value.
EXTRACT Extracts part of aTIME 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.
FIRST_VALUE Gets a value for the first row in the current window frame.
FLOOR Gets the largest integral value that isn't greater thanX.
FORMAT_DATE Formats aDATE value according to a specified format string.
FORMAT_DATETIME Formats aDATETIME value according to a specified format string.
FORMAT_TIME Formats aTIME value according to the 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.
GAP_FILL Finds and fills gaps in a time series.
GENERATE_ARRAY Generates an array of values in a range.
GENERATE_DATE_ARRAY Generates an array of dates in a range.
GENERATE_RANGE_ARRAY Splits a range into an array of subranges.
GENERATE_TIMESTAMP_ARRAY Generates an array of timestamps in a range.
GENERATE_UUID Produces a random universally unique identifier (UUID) as aSTRING value.
GREATEST Gets the greatest value amongX1,...,XN.
GROUPING Checks if a groupable value in theGROUP BY clause is aggregated.
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 DividesX byY, but doesn't generate errors for division by zero or overflow.
INITCAP Formats aSTRING 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.
INT64 Converts a JSON number to a SQLINT64 value.
IS_INF Checks ifX is positive or negative infinity.
IS_NAN Checks ifX is aNaN value.
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_EXTRACT (Deprecated) Extracts a JSON value and converts it to a SQL JSON-formattedSTRING orJSON value.
JSON_EXTRACT_ARRAY (Deprecated) Extracts a JSON array and converts it to a SQLARRAY<JSON-formatted STRING> orARRAY<JSON> value.
JSON_EXTRACT_SCALAR (Deprecated) Extracts a JSON scalar value and converts it to a SQLSTRING value.
JSON_EXTRACT_STRING_ARRAY (Deprecated) Extracts a JSON array of scalar values and converts it to a SQLARRAY<STRING> value.
JSON_FLATTENProduces a new SQLARRAY<JSON> value containing all non-array values that are either directly in the input JSON value or children of one or more consecutively nested arrays in the input JSON value.
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.
KEYS.ADD_KEY_FROM_RAW_BYTES Adds a key to a keyset, and return the new keyset as a serializedBYTES value.
KEYS.KEYSET_CHAIN Produces a Tink keyset that's encrypted with a Cloud KMS key.
KEYS.KEYSET_FROM_JSON Converts aSTRING JSON keyset to a serializedBYTES value.
KEYS.KEYSET_LENGTH Gets the number of keys in the provided keyset.
KEYS.KEYSET_TO_JSON Gets a JSONSTRING representation of a keyset.
KEYS.NEW_KEYSET Gets a serialized keyset containing a new key based on the key type.
KEYS.NEW_WRAPPED_KEYSET Creates a new keyset and encrypts it with a Cloud KMS key.
KEYS.REWRAP_KEYSET Re-encrypts a wrapped keyset with a new Cloud KMS key.
KEYS.ROTATE_KEYSET Adds a new primary cryptographic key to a keyset, based on the key type.
KEYS.ROTATE_WRAPPED_KEYSET Rewraps a keyset and rotates it.
KLL_QUANTILES.EXTRACT_INT64 Gets a selected number of quantiles from anINT64-initialized KLL sketch.
KLL_QUANTILES.EXTRACT_FLOAT64 Gets a selected number of quantiles from aFLOAT64-initialized KLL sketch.
KLL_QUANTILES.EXTRACT_POINT_INT64 Gets a specific quantile from anINT64-initialized KLL sketch.
KLL_QUANTILES.EXTRACT_POINT_FLOAT64 Gets a specific quantile from aFLOAT64-initialized KLL sketch.
KLL_QUANTILES.INIT_INT64 Aggregates values into anINT64-initialized KLL sketch.
KLL_QUANTILES.INIT_FLOAT64 Aggregates values into aFLOAT64-initialized KLL sketch.
KLL_QUANTILES.MERGE_INT64 MergesINT64-initialized KLL sketches into a new sketch, and then gets the quantiles from the new sketch.
KLL_QUANTILES.MERGE_FLOAT64 MergesFLOAT64-initialized KLL sketches into a new sketch, and then gets the quantiles from the new sketch.
KLL_QUANTILES.MERGE_PARTIAL Merges KLL sketches of the same underlying type into a new sketch.
KLL_QUANTILES.MERGE_POINT_INT64 MergesINT64-initialized KLL sketches into a new sketch, and then gets a specific quantile from the new sketch.
KLL_QUANTILES.MERGE_POINT_FLOAT64 MergesFLOAT64-initialized KLL sketches into a new sketch, and then gets a specific quantile from the new sketch.
LAG Gets a value for a preceding row.
LAST_DAY Gets the last day in a specified time period that contains aDATE value.
LAST_DAY Gets the last day in a specified time period that contains aDATETIME value.
LAST_VALUE Gets a value for the last row in the current window frame.
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.
LEAD Gets a value for a subsequent row.
LEAST Gets the least value amongX1,...,XN.
LEFT Gets the specified leftmost portion from aSTRING orBYTES value.
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.
MAX_BY Synonym forANY_VALUE(x HAVING MAX y).
MD5 Computes the hash of aSTRING orBYTES value, using the MD5 algorithm.
MIN Gets the minimum non-NULL value.
MIN_BY Synonym forANY_VALUE(x HAVING MIN y).
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.
NORMALIZE Case-sensitively normalizes the characters in aSTRING value.
NORMALIZE_AND_CASEFOLD Case-insensitively normalizes the characters in aSTRING value.
NTH_VALUE Gets a value for the Nth row of the current window frame.
NTILE Gets the quantile bucket number (1-based) of each row within a window.
OBJ.FETCH_METADATA Fetches Cloud Storage metadata for a partially populatedObjectRef value.
OBJ.GET_ACCESS_URL Returns access URLs for a Cloud Storage object.
OBJ.MAKE_REF Creates anObjectRef value that contains reference information for a Cloud Storage object.
OCTET_LENGTH Alias forBYTE_LENGTH.
PARSE_BIGNUMERIC Converts aSTRING value to aBIGNUMERIC value.
PARSE_DATE Converts aSTRING value to aDATE value.
PARSE_DATETIME Converts aSTRING value to aDATETIME value.
PARSE_JSON Converts a JSON-formattedSTRING value to aJSON value.
PARSE_NUMERIC Converts aSTRING value to aNUMERIC value.
PARSE_TIME Converts aSTRING value to aTIME value.
PARSE_TIMESTAMP Converts aSTRING value to aTIMESTAMP value.
PERCENT_RANK Gets the percentile rank (from 0 to 1) of each row within a window.
PERCENTILE_CONT Computes the specified percentile for a value, using linear interpolation.
PERCENTILE_CONT (Differential Privacy)DIFFERENTIAL_PRIVACY-supportedPERCENTILE_CONT.

Computes a differentially-private percentile across privacy unit columns in a query with aDIFFERENTIAL_PRIVACY clause.
PERCENTILE_DISC Computes the specified percentile for a discrete value.
POW Produces the value ofX raised to the power ofY.
POWER Synonym ofPOW.
RAND Generates a pseudo-random value of typeFLOAT64 in the range of[0, 1).
RANGE Constructs a range ofDATE,DATETIME, orTIMESTAMP values.
RANGE_BUCKET Scans through a sorted array and returns the 0-based position of a point's upper bound.
RANGE_CONTAINS Signature 1: Checks if one range is in another range.

Signature 2: Checks if a value is in a range.
RANGE_ENDGets the upper bound of a range.
RANGE_INTERSECTGets a segment of two ranges that intersect.
RANGE_OVERLAPSChecks if two ranges overlap.
RANGE_SESSIONIZE Produces a table of sessionized ranges.
RANGE_STARTGets the lower bound of a range.
RANK Gets the rank (1-based) of each row within a window.
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 aSTRING value where all substrings that match a regular expression are replaced with a specified value.
REGEXP_SUBSTR Synonym forREGEXP_EXTRACT.
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.
REVERSE Reverses aSTRING orBYTES value.
RIGHT Gets the specified rightmost portion from aSTRING orBYTES value.
ROUND RoundsX to the nearest integer or roundsX toN decimal places after the decimal point.
ROW_NUMBER Gets the sequential row number (1-based) of each row within a window.
RPAD Appends aSTRING orBYTES value with a pattern.
RTRIM Identical to theTRIM function, but only removes trailing characters.
S2_CELLIDFROMPOINT Gets the S2 cell ID covering a pointGEOGRAPHY value.
S2_COVERINGCELLIDS Gets an array of S2 cell IDs that cover aGEOGRAPHY value.
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.
SEARCH Checks to see whether a table or other search data contains a set of search terms.
SEC Computes the secant ofX.
SECH Computes the hyperbolic secant ofX.
SESSION_USER Get the email address or principal identifier of the user that's running the query.
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.
SOUNDEX Gets the Soundex codes for words in aSTRING value.
SPLIT Splits aSTRING orBYTES value, using a delimiter.
SQRT Computes the square root ofX.
ST_ANGLE Takes three pointGEOGRAPHY values, which represent two intersecting lines, and returns the angle between these lines.
ST_AREA Gets the area covered by the polygons in aGEOGRAPHY value.
ST_ASBINARY Converts aGEOGRAPHY value to aBYTES WKB geography value.
ST_ASGEOJSON Converts aGEOGRAPHY value to aSTRING GeoJSON geography value.
ST_ASTEXT Converts aGEOGRAPHY value to aSTRING WKT geography value.
ST_AZIMUTH Gets the azimuth of a line segment formed by two pointGEOGRAPHY values.
ST_BOUNDARY Gets the union of component boundaries in aGEOGRAPHY value.
ST_BOUNDINGBOX Gets the bounding box for aGEOGRAPHY value.
ST_BUFFER Gets the buffer around aGEOGRAPHY value, using a specific number of segments.
ST_BUFFERWITHTOLERANCE Gets the buffer around aGEOGRAPHY value, using tolerance.
ST_CENTROID Gets the centroid of aGEOGRAPHY value.
ST_CENTROID_AGG Gets the centroid of a set ofGEOGRAPHY values.
ST_CLOSESTPOINT Gets the point on aGEOGRAPHY value which is closest to any point in a secondGEOGRAPHY value.
ST_CLUSTERDBSCAN Performs DBSCAN clustering on a group ofGEOGRAPHY values and produces a 0-based cluster number for this row.
ST_CONTAINS Checks if oneGEOGRAPHY value contains anotherGEOGRAPHY value.
ST_CONVEXHULL Returns the convex hull for aGEOGRAPHY value.
ST_COVEREDBY Checks if all points of aGEOGRAPHY value are on the boundary or interior of anotherGEOGRAPHY value.
ST_COVERS Checks if all points of aGEOGRAPHY value are on the boundary or interior of anotherGEOGRAPHY value.
ST_DIFFERENCE Gets the point set difference between twoGEOGRAPHY values.
ST_DIMENSION Gets the dimension of the highest-dimensional element in aGEOGRAPHY value.
ST_DISJOINT Checks if twoGEOGRAPHY values are disjoint (don't intersect).
ST_DISTANCE Gets the shortest distance in meters between twoGEOGRAPHY values.
ST_DUMP Returns an array of simpleGEOGRAPHY components in aGEOGRAPHY value.
ST_DWITHIN Checks if any points in twoGEOGRAPHY values are within a given distance.
ST_ENDPOINT Gets the last point of a linestringGEOGRAPHY value.
ST_EQUALS Checks if twoGEOGRAPHY values represent the sameGEOGRAPHY value.
ST_EXTENT Gets the bounding box for a group ofGEOGRAPHY values.
ST_EXTERIORRING Returns a linestringGEOGRAPHY value that corresponds to the outermost ring of a polygonGEOGRAPHY value.
ST_GEOGFROM Converts aSTRING orBYTES value into aGEOGRAPHY value.
ST_GEOGFROMGEOJSON Converts aSTRING GeoJSON geometry value into aGEOGRAPHY value.
ST_GEOGFROMTEXT Converts aSTRING WKT geometry value into aGEOGRAPHY value.
ST_GEOGFROMWKB Converts aBYTES or hexadecimal-textSTRING WKT geometry value into aGEOGRAPHY value.
ST_GEOGPOINT Creates a pointGEOGRAPHY value for a given longitude and latitude.
ST_GEOGPOINTFROMGEOHASH Gets a pointGEOGRAPHY value that's in the middle of a bounding box defined in aSTRING GeoHash value.
ST_GEOHASH Converts a pointGEOGRAPHY value to aSTRING GeoHash value.
ST_GEOMETRYTYPE Gets the Open Geospatial Consortium (OGC) geometry type for aGEOGRAPHY value.
ST_HAUSDORFFDISTANCEGets the discrete Hausdorff distance between two geometries.
ST_HAUSDORFFDWITHIN Checks if the Hausdorff distance between twoGEOGRAPHY values is within a given distance.
ST_INTERIORRINGS Gets the interior rings of a polygonGEOGRAPHY value.
ST_INTERSECTION Gets the point set intersection of twoGEOGRAPHY values.
ST_INTERSECTS Checks if at least one point appears in twoGEOGRAPHY values.
ST_INTERSECTSBOX Checks if aGEOGRAPHY value intersects a rectangle.
ST_ISCLOSED Checks if all components in aGEOGRAPHY value are closed.
ST_ISCOLLECTION Checks if the total number of points, linestrings, and polygons is greater than one in aGEOGRAPHY value.
ST_ISEMPTY Checks if aGEOGRAPHY value is empty.
ST_ISRING Checks if aGEOGRAPHY value is a closed, simple linestring.
ST_LENGTH Gets the total length of lines in aGEOGRAPHY value.
ST_LINEINTERPOLATEPOINT Gets a point at a specific fraction in a linestringGEOGRAPHY value.
ST_LINELOCATEPOINT Gets a section of a linestringGEOGRAPHY value between the start point and a pointGEOGRAPHY value.
ST_LINESUBSTRING Gets a segment of a single linestring at a specific starting and ending fraction.
ST_MAKELINE Creates a linestringGEOGRAPHY value by concatenating the point and linestring vertices ofGEOGRAPHY values.
ST_MAKEPOLYGON Constructs a polygonGEOGRAPHY value by combining a polygon shell with polygon holes.
ST_MAKEPOLYGONORIENTED Constructs a polygonGEOGRAPHY value, using an array of linestringGEOGRAPHY values. The vertex ordering of each linestring determines the orientation of each polygon ring.
ST_MAXDISTANCE Gets the longest distance between two non-emptyGEOGRAPHY values.
ST_NPOINTS An alias ofST_NUMPOINTS.
ST_NUMGEOMETRIES Gets the number of geometries in aGEOGRAPHY value.
ST_NUMPOINTS Gets the number of vertices in the aGEOGRAPHY value.
ST_PERIMETER Gets the length of the boundary of the polygons in aGEOGRAPHY value.
ST_POINTN Gets the point at a specific index of a linestringGEOGRAPHY value.
ST_REGIONSTATS Computes statistics describing the pixels in a geospatial raster image that intersect aGEOGRAPHY value.
ST_SIMPLIFY Converts aGEOGRAPHY value into a simplifiedGEOGRAPHY value, using tolerance.
ST_SNAPTOGRID Produces aGEOGRAPHY value, where each vertex has been snapped to a longitude/latitude grid.
ST_STARTPOINT Gets the first point of a linestringGEOGRAPHY value.
ST_TOUCHES Checks if twoGEOGRAPHY values intersect and their interiors have no elements in common.
ST_UNION Gets the point set union of multipleGEOGRAPHY values.
ST_UNION_AGG Aggregates overGEOGRAPHY values and gets their point set union.
ST_WITHIN Checks if oneGEOGRAPHY value contains anotherGEOGRAPHY value.
ST_X Gets the longitude from a pointGEOGRAPHY value.
ST_Y Gets the latitude from a pointGEOGRAPHY value.
STARTS_WITH Checks if aSTRING orBYTES value is a prefix of another value.
STDDEV An alias of theSTDDEV_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 (JSON) Converts a JSON string to a SQLSTRING 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.
SUBSTR Gets a portion of aSTRING orBYTES value.
SUBSTRINGAlias forSUBSTR
SUM Gets the sum of non-NULL values.
SUM (Differential Privacy)DIFFERENTIAL_PRIVACY-supportedSUM.

Gets the differentially-private sum of non-NULL, non-NaN values in a query with aDIFFERENTIAL_PRIVACY clause.
TAN Computes the tangent ofX.
TANH Computes the hyperbolic tangent ofX.
TEXT_ANALYZE Extracts terms (tokens) from text and converts them into a tokenized document.
TF_IDF Evaluates how relevant a term (token) is to a tokenized document in a set of tokenized documents.
TIME Constructs aTIME value.
TIME_ADD Adds a specified time interval to aTIME value.
TIME_DIFF Gets the number of unit boundaries between twoTIME values at a particular time granularity.
TIME_SUB Subtracts a specified time interval from aTIME value.
TIME_TRUNC Truncates aTIME value at a particular granularity.
TIMESTAMP Constructs aTIMESTAMP value.
TIMESTAMP_ADD Adds a specified time interval to aTIMESTAMP value.
TIMESTAMP_BUCKET Gets the lower bound of the timestamp bucket that contains a timestamp.
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 orDATETIME 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 a SQL value to a JSON-formattedSTRING 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 aSTRING orBYTES value.
TRUNC Rounds a number likeROUND(X) orROUND(X, N), but always rounds towards zero and never overflows.
TYPEOF Gets the name of the data type for an expression.
UNICODE Gets the Unicode code point for the first character in a value.
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_POP Computes the population (biased) variance of the values.
VAR_SAMP Computes the sample (unbiased) variance of the values.
VARIANCE An alias ofVAR_SAMP.
VECTOR_SEARCH Performs a vector search on embeddings to find semantically similar entities.
VECTOR_INDEX.STATISTICS Calculate how much an indexed table's data has drifted between when a vector index was trained and the present.

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.