Console
    Start free

    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.
    APPROX_COUNT_DISTINCT Gets the approximate result for COUNT(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_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_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.
    BIT_AND Performs a bitwise AND operation on an 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 isn't 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.
    CORR Computes the Pearson coefficient of correlation of a set of number pairs.
    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 .
    COUNTIF Gets the number of TRUE 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 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_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.
    DIV Divides integer X by integer Y .
    ENDS_WITH Checks if a STRING or BYTES value is the suffix of another value.
    EXP Computes e to the power of X .
    EXTRACT Extracts part of a date from a DATE value.
    EXTRACT Extracts part of a TIMESTAMP value.
    EUCLIDEAN_DISTANCE Computes the Euclidean distance between two vectors.
    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_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 doesn't 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.
    MAP_CONTAINS_KEY Checks if a key is in a map.
    MAP_EMPTY Checks if a map is empty.
    MAP_ENTRIES Gets an array of key-value pairs from a map, sorted in ascending order by key.
    MAP_KEYS Gets an array of keys from a map, sorted in ascending order.
    MAP_VALUES Gets an array of values from a map, sorted in ascending order by key.
    MAX Gets the maximum non- NULL value.
    MIN Gets the minimum non- NULL value.
    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.
    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) .
    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 .
    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 .
    ST_ACCUM Aggregates GEOGRAPHY values into an array of GEOGRAPHY elements.
    ST_ANGLE Takes three point GEOGRAPHY values, which represent two intersecting lines, and returns the angle between these lines.
    ST_AREA Gets the area covered by the polygons in a GEOGRAPHY value.
    ST_ASBINARY Converts a GEOGRAPHY value to a BYTES WKB geography value.
    ST_ASGEOJSON Converts a GEOGRAPHY value to a STRING GeoJSON geography value.
    ST_ASKML Converts a GEOGRAPHY value to a STRING KML geometry value.
    ST_ASTEXT Converts a GEOGRAPHY value to a STRING WKT geography value.
    ST_AZIMUTH Gets the azimuth of a line segment formed by two point GEOGRAPHY values.
    ST_BOUNDARY Gets the union of component boundaries in a GEOGRAPHY value.
    ST_BOUNDINGBOX Gets the bounding box for a GEOGRAPHY value.
    ST_BUFFER Gets the buffer around a GEOGRAPHY value, using a specific number of segments.
    ST_BUFFERWITHTOLERANCE Gets the buffer around a GEOGRAPHY value, using tolerance.
    ST_CENTROID Gets the centroid of a GEOGRAPHY value.
    ST_CLOSESTPOINT Gets the point on a GEOGRAPHY value which is closest to any point in a second GEOGRAPHY value.
    ST_CONTAINS Checks if one GEOGRAPHY value contains another GEOGRAPHY value.
    ST_CONVEXHULL Returns the convex hull for a GEOGRAPHY value.
    ST_COVEREDBY Checks if all points of a GEOGRAPHY value are on the boundary or interior of another GEOGRAPHY value.
    ST_COVERS Checks if all points of a GEOGRAPHY value are on the boundary or interior of another GEOGRAPHY value.
    ST_DIFFERENCE Gets the point set difference between two GEOGRAPHY values.
    ST_DIMENSION Gets the dimension of the highest-dimensional element in a GEOGRAPHY value.
    ST_DISJOINT Checks if two GEOGRAPHY values are disjoint (don't intersect).
    ST_DISTANCE Gets the shortest distance in meters between two GEOGRAPHY values.
    ST_DUMP Returns an array of simple GEOGRAPHY components in a GEOGRAPHY value.
    ST_DUMPPOINTS Produces an array of GEOGRAPHY points with all points, line vertices, and polygon vertices in a GEOGRAPHY value.
    ST_DWITHIN Checks if any points in two GEOGRAPHY values are within a given distance.
    ST_ENDPOINT Gets the last point of a linestring GEOGRAPHY value.
    ST_EQUALS Checks if two GEOGRAPHY values represent the same GEOGRAPHY value.
    ST_EXTENT Gets the bounding box for a group of GEOGRAPHY values.
    ST_EXTERIORRING Returns a linestring GEOGRAPHY value that corresponds to the outermost ring of a polygon GEOGRAPHY value.
    ST_GEOGFROM Converts a STRING or BYTES value into a GEOGRAPHY value.
    ST_GEOGFROMGEOJSON Converts a STRING GeoJSON geometry value into a GEOGRAPHY value.
    ST_GEOGFROMKML Converts a STRING KML geometry value into a GEOGRAPHY value.
    ST_GEOGFROMTEXT Converts a STRING WKT geometry value into a GEOGRAPHY value.
    ST_GEOGFROMWKB Converts a BYTES or hexadecimal-text STRING WKT geometry value into a GEOGRAPHY value.
    ST_GEOGPOINT Creates a point GEOGRAPHY value for a given longitude and latitude.
    ST_GEOGPOINTFROMGEOHASH Gets a point GEOGRAPHY value that's in the middle of a bounding box defined in a STRING GeoHash value.
    ST_GEOHASH Converts a point GEOGRAPHY value to a STRING GeoHash value.
    ST_GEOMETRYTYPE Gets the Open Geospatial Consortium (OGC) geometry type for a GEOGRAPHY value.
    ST_HAUSDORFFDISTANCE Gets the discrete Hausdorff distance between two geometries.
    ST_HAUSDORFFDWITHIN Checks if the Hausdorff distance between two GEOGRAPHY values is within a given distance.
    ST_INTERIORRINGS Gets the interior rings of a polygon GEOGRAPHY value.
    ST_INTERSECTION Gets the point set intersection of two GEOGRAPHY values.
    ST_INTERSECTS Checks if at least one point appears in two GEOGRAPHY values.
    ST_INTERSECTSBOX Checks if a GEOGRAPHY value intersects a rectangle.
    ST_ISCLOSED Checks if all components in a GEOGRAPHY value are closed.
    ST_ISCOLLECTION Checks if the total number of points, linestrings, and polygons is greater than one in a GEOGRAPHY value.
    ST_ISEMPTY Checks if a GEOGRAPHY value is empty.
    ST_ISRING Checks if a GEOGRAPHY value is a closed, simple linestring.
    ST_LENGTH Gets the total length of lines in a GEOGRAPHY value.
    ST_LINEINTERPOLATEPOINT Gets a point at a specific fraction in a linestring GEOGRAPHY value.
    ST_LINELOCATEPOINT Gets a section of a linestring GEOGRAPHY value between the start point and a point GEOGRAPHY value.
    ST_LINESUBSTRING Gets a segment of a single linestring at a specific starting and ending fraction.
    ST_MAKELINE Creates a linestring GEOGRAPHY value by concatenating the point and linestring vertices of GEOGRAPHY values.
    ST_MAKEPOLYGON Constructs a polygon GEOGRAPHY value by combining a polygon shell with polygon holes.
    ST_MAKEPOLYGONORIENTED Constructs a polygon GEOGRAPHY value, using an array of linestring GEOGRAPHY values. The vertex ordering of each linestring determines the orientation of each polygon ring.
    ST_MAXDISTANCE Gets the longest distance between two non-empty GEOGRAPHY values.
    ST_NPOINTS An alias of ST_NUMPOINTS .
    ST_NUMGEOMETRIES Gets the number of geometries in a GEOGRAPHY value.
    ST_NUMPOINTS Gets the number of vertices in the a GEOGRAPHY value.
    ST_PERIMETER Gets the length of the boundary of the polygons in a GEOGRAPHY value.
    ST_POINTN Gets the point at a specific index of a linestring GEOGRAPHY value.
    ST_REGIONSTATS Computes statistics describing the pixels in a geospatial raster image that intersect a GEOGRAPHY value.
    ST_SIMPLIFY Converts a GEOGRAPHY value into a simplified GEOGRAPHY value, using tolerance.
    ST_SNAPTOGRID Produces a GEOGRAPHY value, where each vertex has been snapped to a longitude/latitude grid.
    ST_STARTPOINT Gets the first point of a linestring GEOGRAPHY value.
    ST_TOUCHES Checks if two GEOGRAPHY values intersect and their interiors have no elements in common.
    ST_UNION Gets the point set union of multiple GEOGRAPHY values.
    ST_UNION_AGG Aggregates over GEOGRAPHY values and gets their point set union.
    ST_WITHIN Checks if one GEOGRAPHY value contains another GEOGRAPHY value.
    STARTS_WITH Checks if a STRING or BYTES value is a prefix of another value.
    STDDEV An alias of the STDDEV_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 (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.
    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 .
    Temporal filters Access temporal elements of a table by using the Bigtable table name as a function.
    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_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_FLOAT32 Converts the big-endian bytes of a 32-bit IEEE 754 floating point number into a FLOAT32 value.
    TO_FLOAT64 Converts the big-endian bytes of a 64-bit IEEE 754 floating point number into a FLOAT64 value.
    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.
    UNPACK Expands the timestamped values in an input row into multiple rows, each row representing a different timestamp; and moves the timestamps into a `_timestamp` column.
    UPPER Formats alphabetic characters in a STRING value as uppercase.

    Formats ASCII characters in a BYTES 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 of VAR_SAMP .
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: