This topic is a compilation of functions, operators, and conditional expressions. Show
To learn more about how to call functions, function call rules, the OPERATORS AND CONDITIONALSOperatorsOperators are represented by special characters or keywords; they do not use function call syntax. An operator manipulates any number of data inputs, also called operands, and returns a result. Common conventions:
Operator precedenceThe following table lists all BigQuery operators from highest to lowest precedence, i.e. the order in which they will be evaluated within a statement.
Operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right. For example, the expression:
is interpreted as
The expression:
is interpreted as:
All comparison operators have the same priority, but comparison operators are not associative. Therefore, parentheses are required in order to resolve ambiguity. For example:
Field access operator
Description Gets the value of a field. Alternatively known as the dot operator. Can be used to access nested fields. For example, Input types
Return type
Example In the following example, the expression is
Array subscript operator
Description Gets a value from an array at a specific location. Input types
Return type
Examples In this
example, the array subscript operator is used to return values at specific locations in
In the following example, when you reference an index in an array that is out of range and the
JSON subscript operator
Description Gets a value of an array element or field in a JSON expression. Can be used to access nested data. Input types
Return type
Example In the following example:
Arithmetic operatorsAll arithmetic operators accept input of numeric type T, and the result type has type T unless otherwise indicated in the description below:
NOTE: Divide by zero operations return an error. To return a different result, consider the IEEE_DIVIDE or SAFE_DIVIDE functions. Result types for Addition, Subtraction and Multiplication:
Result types for Division:
Result types for Unary Plus:
Result types for Unary Minus:
Date arithmetics operatorsOperators '+' and '-' can be used for arithmetic operations on dates.
Description Adds or subtracts Return Data Type DATE Example
Datetime subtraction
Description Computes the difference between two datetime values as an interval. Return Data Type INTERVAL Example
Interval arithmetic operatorsAddition and subtraction
Description Adds an interval to a datetime value or subtracts an interval from a datetime value. Example
Multiplication and division
Description Multiplies or divides an interval value by an integer. Example
Bitwise operatorsAll bitwise operators return the same type and the same length as the first operand.
Logical operatorsBigQuery supports the
Examples The examples in this section reference a table called
Comparison operatorsComparisons always return BOOL. Comparisons generally require both operands to be of the same type. If operands are of different types, and if BigQuery can convert the values of those types to a common type without loss of precision, BigQuery will generally coerce them to that common type for the comparison; BigQuery will generally coerce literals to the type of non-literals, where present. Comparable data types are defined in Data Types. STRUCTs support only 4 comparison operators: equal (=), not equal (!= and <>), and IN. The following rules apply when comparing these data types:
When testing values that have a STRUCT data type for equality, it's possible that one or more fields are
The following table demonstrates how STRUCT data types are compared when they have fields that are
EXISTS operator
Description Returns Examples In this example, the
IN operatorThe
Description Checks for an equal value in a set of values.
Semantic rules apply, but in general,
Semantic rules When using the
When using the
This operator generally supports collation, however, The semantics of:
are defined as equivalent to:
and the subquery and array forms are defined similarly.
is equivalent to:
The
This form is often used with
See the Arrays topic for more information on how to use this syntax.
See the Struct Type for more information. Return Data Type
Examples You can use these
Example with
Example with
Example with
Example with
Example with
IS operatorsIS operators return TRUE or FALSE for the condition they are testing. They never return
IS DISTINCT FROM operator
Description
You can use this operation with fields in a complex data type, but not on the complex data types themselves. These complex data types cannot be compared directly:
Input types
Return type
Examples These return
These return
Concatenation operatorThe concatenation operator combines multiple values into one.
Conditional expressionsConditional expressions impose constraints on the evaluation order of their inputs. In essence, they are evaluated left to right, with short-circuiting, and only evaluate the output value that was chosen. In contrast, all inputs to regular functions are evaluated before calling the function. Short-circuiting in conditional expressions can be exploited for error handling or performance tuning. CASE expr
Description Compares
This expression supports specifying collation. Return Data Type Supertype of Example
CASE
Description Evaluates the
condition of each successive
This expression supports specifying collation. Return Data Type Supertype
of Example
COALESCE
Description Returns the value of the first non-null expression. The remaining expressions are not evaluated. An input expression can be any type. There may be multiple input expression types. All input expressions must be implicitly coercible to a common supertype. Return Data Type Supertype of Examples
IF
Description If
Return Data Type Supertype of Example
IFNULL
Description If
Return Data Type Supertype of Examples
NULLIF
Description Returns NULL if
This expression supports specifying collation. Return Data Type Supertype of Example
FUNCTIONSAggregate functionsThe following general aggregate functions are available in Google Standard SQL. To learn about the syntax for aggregate function calls, see Aggregate function calls. ANY_VALUE
Description Returns
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Supported Argument Types Any Returned Data Types Matches the input data type. Examples
ARRAY_AGG
Description Returns an ARRAY of To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the An error is raised if an array in the final query result contains a Supported Argument Types All data types except ARRAY. Returned Data Types ARRAY If there are zero input rows, this function returns Examples
ARRAY_CONCAT_AGG
Description Concatenates elements from This
function ignores To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types
Examples
AVG
Description Returns the average of non- To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Supported Argument Types Any numeric input type, such as INT64. Note that, for floating point input types, the return result is non-deterministic, which means you might receive a different result each time you use this function. Returned Data Types
Examples
BIT_AND
Description Performs a bitwise AND operation on To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types INT64 Examples
BIT_OR
Description Performs a bitwise OR operation on To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types INT64 Examples
BIT_XOR
Description Performs a bitwise XOR operation on To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types INT64 Examples
COUNT1.
2.
Description
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the This function with DISTINCT supports specifying collation. Supported Argument Types
Return Data Types INT64 Examples You can use the
If you want to count the number of distinct values of an expression for which a certain condition is satisfied, this is one recipe that you can use:
Here, For example, to count the number of distinct positive values of
Or to count the number of distinct dates on which a certain kind of event occurred:
COUNTIF
Description Returns the count of Since
Note that this uses To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Supported Argument Types BOOL Return Data Types INT64 Examples
LOGICAL_AND
Description Returns the logical AND of all non- To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types BOOL Return Data Types BOOL Examples
LOGICAL_OR
Description Returns the logical OR of all non- To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types BOOL Return Data Types BOOL Examples
MAX
Description Returns the maximum value of non- To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the This function supports specifying collation. Supported Argument Types Any orderable data type. Return Data Types Same as the data type used as the input values. Examples
MIN
Description Returns the minimum value of non- To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the This function supports specifying collation. Supported Argument Types Any orderable data type. Return Data Types Same as the data type used as the input values. Examples
STRING_AGG
Description Returns a value (either If a To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Supported Argument Types Either Return Data Types Either
Examples
SUM
Description Returns the sum of non-null values. If the expression is a floating point value, the sum is non-deterministic, which means you might receive a different result each time you use this function. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Supported Argument Types Any supported numeric data types and INTERVAL. Return Data Types
Special cases: Returns Returns Returns Returns Returns Returns Examples
Statistical aggregate functionsThe following statistical aggregate functions are available in Google Standard SQL. To learn about the syntax for aggregate function calls, see Aggregate function calls. CORR
Description Returns
the Pearson coefficient of correlation of a set of number pairs. For each number pair, the first number is the dependent variable and the second number is the independent variable. The return result is between All numeric types are supported. If the input is This function ignores any input pairs that contain one or more NULL values. If there are fewer than two input pairs without NULL values, this function returns NULL. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Return Data Type
COVAR_POP
Description Returns the population covariance of a set of number pairs. The first number is the dependent variable; the second number is the independent variable. The return result is between All numeric types are supported. If the input is This function ignores any input pairs that contain one or more NULL values. If there is no input pair without NULL values, this function returns NULL. If there is exactly one input pair without NULL values, this function returns 0. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Return Data Type
COVAR_SAMP
Description Returns the sample covariance of a set of number pairs. The first number is the dependent variable; the second number is the independent variable. The return result is between All numeric types are supported. If the input is This function ignores any input pairs that contain one or more NULL values. If there are fewer than two input pairs without NULL values, this function returns NULL. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Return Data Type
STDDEV_POP
Description Returns the population (biased) standard deviation of the values. The return result is between All numeric types are supported. If the input is
This function ignores any NULL inputs. If all inputs are ignored, this function returns NULL. If this function receives a single non-NULL input, it returns To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Return Data Type
STDDEV_SAMP
Description Returns the sample (unbiased) standard deviation of the values. The return result is between All numeric types are supported. If the input is This function ignores any NULL inputs. If there are fewer than two non-NULL inputs, this function returns NULL. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Return Data Type
STDDEV
Description An alias of STDDEV_SAMP. VAR_POP
Description Returns the population (biased) variance of the values. The return result is between All numeric types are supported. If the
input is This function ignores any NULL inputs. If all inputs are ignored, this function returns NULL. If this function receives a single non-NULL input, it returns To learn more about the Return Data Type
VAR_SAMP
Description Returns the sample (unbiased) variance of the values. The return result is between All numeric types are supported. If the input is This function ignores any NULL inputs. If there are fewer than two non-NULL inputs, this function returns NULL. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the Return Data Type
VARIANCE
Description An alias of VAR_SAMP. Approximate aggregate functionsThe following approximate aggregate functions are available in Google Standard SQL. To learn about the syntax for aggregate function calls, see Aggregate function calls. Approximate aggregate functions are scalable in terms of memory usage and time, but produce approximate results instead of exact results. These functions typically require less memory than exact aggregation
functions like The approximate aggregate functions in this section work directly on the input data, rather than an intermediate estimation of the data. These functions do not allow users to specify the precision for the estimation with sketches. If you would like specify precision with sketches, see:
APPROX_COUNT_DISTINCT
Description Returns the approximate result for This function is less accurate than Supported Argument Types Any data type except: Returned Data Types INT64 Examples
APPROX_QUANTILES
Description Returns the approximate boundaries for a group of Returns To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types
Examples
APPROX_TOP_COUNT
Description Returns the approximate top elements of Each Returns To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types
Examples
NULL handling
APPROX_TOP_SUM
Description Returns the approximate top elements of If the The elements are returned as an array of Returns To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. Supported Argument Types
Returned Data Types
Examples
NULL handling
HyperLogLog++ functionsThe HyperLogLog++ algorithm (HLL++) estimates cardinality from sketches. HLL++ functions are approximate aggregate functions. Approximate aggregation typically requires less memory than exact aggregation functions, like If you do not need materialized sketches, you can alternatively use an approximate aggregate function with system-defined precision, such as
BigQuery supports the following HLL++ functions: HLL_COUNT.INIT
Description An aggregate function that takes one or more This function supports an optional parameter, If the input is For more information, see HyperLogLog in Practice: Algorithmic Engineering of a State of The Art Cardinality Estimation Algorithm. Supported input types
Return type
Example The following query creates HLL++ sketches that count the number of distinct users with at least one invoice per country.
HLL_COUNT.MERGE
Description An aggregate function that returns the cardinality of several HLL++ set sketches by computing their union. Each If the merged sketches were initialized with different precisions, the precision will be downgraded to the lowest precision involved in the merge. This function ignores Supported input types
Return type
Example The following query counts the number of distinct users across all countries who have at least one invoice.
HLL_COUNT.MERGE_PARTIAL
Description An aggregate function that takes one or more HLL++ Each If the merged sketches were initialized with different precisions, the precision will be downgraded to the lowest precision involved in the merge. For example, if This function returns Supported input types
Return type
Example The following query returns an HLL++ sketch that counts the number of distinct users who have at least one invoice across all countries.
Description A scalar function that extracts a cardinality estimate of a single HLL++ sketch. If Supported input types
Return type
Example The following query returns the number of distinct users for each country who have at least one invoice.
Numbering functionsThe following sections describe the numbering functions that BigQuery supports. Numbering functions are a subset of window functions. To create a window function call and learn about the syntax for window functions, see Window function_calls. Numbering functions assign integer values to each row based on their position within the specified window. The RANK
Description Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value, instead of To learn more about the Return Type
Examples
DENSE_RANK
Description Returns the ordinal (1-based) rank of each row within the window partition. All peer rows receive the same rank value, and the subsequent rank value is incremented by one. To learn more about the Return Type
Examples
PERCENT_RANK
Description Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK is the To learn more about the Return Type
Example
CUME_DIST
Description Return the relative rank of a row defined as NP/NR. NP is defined to be the number of rows that either precede or are peers with the current row. NR is the number of rows in the partition. To learn more about the Return Type
Example
NTILE
Description This function
divides the rows into To learn more about the Return Type
Example
ROW_NUMBER
Description Does not require the To learn more about the Return Type
Examples
Bit functionsBigQuery supports the following bit functions. BIT_COUNT
Description The input, Returns the number of bits that are set in the input Return Data Type INT64 Example
Conversion functionsBigQuery supports the following conversion functions. These data type conversions are explicit, but some conversions can happen implicitly. You can learn more about implicit and explicit conversion here. CAST overview
Description Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type. When
using Casts between supported types that do not successfully map from the original value to the target domain produce runtime errors. For example, casting BYTES to STRING where the byte sequence is not valid UTF-8 results in a runtime error. Some casts can include a format clause, which provides instructions for how to conduct the cast. For example, you could instruct a cast to convert a sequence of bytes to a BASE64-encoded string instead of a UTF-8-encoded string. The structure of the format clause is unique to each type of cast and more information is available in the section for that cast. Examples The following query results in
CAST AS ARRAY
Description BigQuery supports casting to
ARRAY. The
Conversion rules
CAST AS BIGNUMERIC
Description BigQuery supports casting to BIGNUMERIC. The
Conversion rules
CAST AS BOOL
Description BigQuery supports casting to BOOL. The
Conversion rules
CAST AS BYTES
Description BigQuery supports casting to BYTES. The
Format clause When an expression of one type is cast to another
type, you can use the format clause to provide instructions for how to conduct the cast. You can use the format clause in this section if
Conversion rules
CAST AS DATE
Description BigQuery supports casting to DATE. The
Format clause When an expression of
one type is cast to another type, you can use the format clause to provide instructions for how to conduct the cast. You can use the format clause in this section if
Conversion rules
CAST AS DATETIME
Description BigQuery supports casting to DATETIME. The
Format clause When an
expression of one type is cast to another type, you can use the format clause to provide instructions for how to conduct the cast. You can use the format clause in this section if
Conversion rules
CAST AS FLOAT64
Description BigQuery supports casting to floating point types. The
Conversion rules
CAST AS INT64
Description BigQuery supports casting to integer types. The
Conversion rules
Examples If you are working with hex strings (
CAST AS INTERVAL
Description BigQuery supports casting to INTERVAL. The
Conversion rules
Examples
CAST AS NUMERIC
Description BigQuery supports casting to NUMERIC. The
Conversion rules
CAST AS STRING
Description BigQuery supports casting to STRING. The
Format clause When an expression of one type is cast to another type, you can use the format clause to provide instructions for how to conduct the cast. You can use the format clause in this section if
The format clause for For more information, see the following topics:
Conversion rules
Examples
CAST AS STRUCT
Description BigQuery supports casting to STRUCT. The
Conversion rules
CAST AS TIME
Description BigQuery supports casting to TIME. The
Format clause When an expression of
one type is cast to another type, you can use the format clause to provide instructions for how to conduct the cast. You can use the format clause in this section if
Conversion rules
CAST AS TIMESTAMP
Description BigQuery supports casting to TIMESTAMP. The
Format clause When
an expression of one type is cast to another type, you can use the format clause to provide instructions for how to conduct the cast. You can use the format clause in this section if
The format clause for Conversion rules
Examples The following example casts a string-formatted timestamp as a timestamp:
The following examples cast a string-formatted date and time as a timestamp. These examples return the same output as the previous example.
PARSE_BIGNUMERIC
Description Converts a string to a The numeric literal contained
in the string must not exceed the maximum precision or range of the
This
funcion is similar to using the
Rules for valid input strings:
Return Data Type
Examples This example shows an input with spaces before, after, and between the sign and the number:
This example shows an input with an exponent as well as the sign after the number:
This example shows an input with multiple commas in the integer part of the number:
This example shows an input with a decimal point and no digits in the whole number part:
Examples of invalid inputs This example is invalid because the whole number part contains no digits:
This example is invalid because there are whitespaces between digits:
This example is invalid because the number is empty except for an exponent:
This example is invalid because the string contains multiple signs:
This example is invalid because the value of the number falls outside the range of
This example is invalid because the string contains invalid characters:
PARSE_NUMERIC
Description Converts a string to a The numeric literal contained in the string must not exceed the maximum precision or range of the
This function is similar to using the
Rules for valid input strings:
Return Data Type
Examples This example shows an input with spaces before, after, and between the sign and the number:
This example shows an input with an exponent as well as the sign after the number:
This example shows an input with multiple commas in the integer part of the number:
This example shows an input with a decimal point and no digits in the whole number part:
Examples of invalid inputs This example is invalid because the whole number part contains no digits:
This example is invalid because there are whitespaces between digits:
This example is invalid because the number is empty except for an exponent:
This example is invalid because the string contains multiple signs:
This example is invalid because the value of the number falls outside the range of
This example is invalid because the string contains invalid characters:
SAFE_CAST
Description When using
If you want to protect your queries from these types of errors, you can use
If you are casting from bytes to strings, you can also use the function, Other conversion functionsYou can learn more about these conversion functions elsewhere in the documentation:
Format clause for CAST
The format clause can be used in some The format clause includes a format model. The format model can contain format elements combined together as a format string. Format bytes as string
You can cast a sequence of bytes to a string with a format element in the format string. If the bytes cannot be formatted with a format element, an error is returned. If the sequence of bytes is
Return type
Example
Format string as bytes
You can cast a string to bytes with a format element in the format string. If the string cannot be formatted with the format element, an error is returned. Format elements are case-insensitive. In the string expression, whitespace characters, such as
Return type
Example
Format date and time as stringYou can format these date and time parts as a string:
Case matching is supported when you format some date or time parts as a string and the output contains letters. To learn more, see Case matching. Case matchingWhen the output of some format element contains letters, the letter cases of the output is matched with the letter cases of the format element, meaning the words in the output are capitalized according to how the format element is capitalized. This is called case matching. The rules are:
Format year part as string
Casts a data type that contains the year part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a year part:
If
Return type
Example
Format month part as string
Casts a data type that contains the month part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a month part:
If
Return type
Example
Format day part as string
Casts a data type that contains the day part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a day part:
If
Return type
Example
Format hour part as string
Casts a data type that contains the hour part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a hour part:
If
Return type
Examples
Format minute part as string
Casts a data type that contains the minute part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a minute part:
If
Return type
Example
Format second part as string
Casts a data type that contains the second part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a second part:
If
Return type
Examples
Format meridian indicator part as string
Casts a data type that contains the meridian indicator part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a meridian indicator part:
If
Return type
Examples
Format time zone part as string
Casts a data type that contains the time zone part to a string. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a time zone part:
If
Return type
Examples
Format literal as string
Format string as date and timeYou can format a string with these date and time parts:
When formatting a string with date and time parts, you must follow the format model rules. Format model rulesWhen casting a string to date and time parts, you must ensure the format model is valid. The format model represents the elements passed into
Format string as year part
Casts a string-formatted year to a data type that contains the year part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a year part:
If the An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as month part
Casts a string-formatted month to a data type that contains the month part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a month part:
If the An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as day part
Casts a string-formatted day to a data type that contains the day part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a day part:
If the An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as hour part
Casts a string-formatted hour to a data type that contains the hour part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a hour part:
If the An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as minute part
Casts a string-formatted minute to a data type that contains the minute part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a minute part:
If the An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as second part
Casts a string-formatted second to a data type that contains the second part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a second part:
If the An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as meridian indicator part
Casts a string-formatted meridian indicator to a data type that contains the meridian indicator part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a meridian indicator part:
An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as time zone part
Casts a string-formatted time zone to a data type that contains the time zone part. Includes format elements, which provide instructions for how to conduct the cast.
These data types include a time zone part:
An error is generated if a value that is not a supported format element appears in
Return type The data type to which the string was cast. This can be:
Examples
Format string as literal
Format numeric type as string
You can cast a numeric type to a string by combining the following format elements:
Except for the exponent format element ( Return type
Example
Format digits as stringThe following format elements output digits. If there aren't enough digit format elements to represent the input, all digit format elements are
replaced with
Return type
Example
Format decimal point as stringThe following format elements output a decimal point. These format elements are mutually exclusive. At most one can appear in the format string.
Return type
Example
Format sign as stringThe following format elements output the sign (+/-). These format elements are mutually exclusive. At most one can appear in the format string. If there are no sign format elements, one extra space is reserved for the sign. For example, if the input is The sign appears before the number. If the format model includes a currency symbol element, then the sign appears before the currency symbol.
Return type
Example
Format currency symbol as stringThe following format elements output a currency symbol. These format elements are mutually exclusive. At most one can appear in the format string. In the output, the currency symbol appears before the first digit or decimal point.
Return type
Example
Format group separator as stringThe following format elements output a group separator.
Return type
Example
Other numeric format elements
Return type
Example
About BASE encodingBASE encoding translates binary data in string format into a radix-X representation. If X is 2, 8, or 16, Arabic numerals 0–9 and the Latin letters a–z are used in the encoded string. So for example, BASE16/Hexadecimal encoding results contain 0~9 and a~f. If X is 32 or 64, the default character tables are defined in rfc 4648. When you decode a BASE string where X is 2, 8, or 16, the Latin letters in the input string are case-insensitive. For example, both "3a" and "3A" are valid input strings for BASE16/Hexadecimal decoding, and will output the same result. Mathematical functionsAll mathematical functions have the following behaviors:
ABS
Description Computes absolute value. Returns an error if the argument is an integer and the output value cannot be represented as the same type; this happens only for the largest negative input value, which has no positive representation.
Return Data Type
SIGN
Description Returns
Return Data Type
IS_INF
Description Returns
IS_NAN
Description Returns
IEEE_DIVIDE
Description Divides X by Y; this function never fails. Returns
RAND
Description Generates a pseudo-random value of type SQRT
Description Computes the square root of X. Generates an error if X is less than 0.
Return Data Type
POW
Description Returns the value of X raised to the power of Y. If the result underflows and is not representable, then the function returns a value of zero.
Return Data Type The return data type is determined by the argument types with the following table.
POWER
Description Synonym of EXP
Description Computes e to the power of X, also called the natural exponential function. If the result underflows, this function returns a zero. Generates an error if the result overflows.
Return Data Type
LN
Description Computes the natural logarithm of X. Generates an error if X is less than or equal to zero.
Return Data Type
LOG
Description If only X is present,
Return Data Type
LOG10
Description Similar to
Return Data Type
GREATEST
Description Returns the greatest value among
This function supports specifying collation. Return Data Types Data type of the input values. LEAST
Description Returns the least value among
This function supports specifying collation. Return Data Types Data type of the input values. DIV
Description Returns the result of integer division of X by Y. Division by zero returns an error. Division by -1 may overflow.
Return Data Type The return data type is determined by the argument types with the following table.
SAFE_DIVIDE
Description Equivalent to the division operator (
Return Data Type
SAFE_MULTIPLY
Description Equivalent to the multiplication operator (
Return Data Type
SAFE_NEGATE
Description Equivalent to the unary minus operator (
Return Data Type
SAFE_ADD
Description Equivalent to the addition operator (
Return Data Type
SAFE_SUBTRACT
Description Returns the result of Y subtracted from X. Equivalent to the subtraction operator (
Return Data Type
MOD
Description Modulo function: returns the remainder of the division of X by Y. Returned value has the same sign as X. An error is generated if Y is 0.
Return Data Type The return data type is determined by the argument types with the following table.
ROUND
Description If only X is present, rounds X to the nearest integer. If N is present, rounds X to N decimal places after the decimal point. If N is negative, rounds off digits to the left of the decimal point. Rounds halfway cases away from zero. Generates an error if overflow occurs. If X is a
If you set the
Return Data Type
TRUNC
Description If only X is present,
Return Data Type
CEIL
Description Returns the smallest integral value that is not less than X.
Return Data Type
CEILING
Description Synonym of CEIL(X) FLOOR
Description Returns the largest integral value that is not greater than X.
Return Data Type
COS
Description Computes the cosine of X where X is specified in radians. Never fails.
COSH
Description Computes the hyperbolic cosine of X where X is specified in radians. Generates an error if overflow occurs.
ACOS
Description Computes the principal value of the inverse cosine of X. The return value is in the range [0,π]. Generates an error if X is a value outside of the range [-1, 1].
ACOSH
Description Computes the inverse hyperbolic cosine of X. Generates an error if X is a value less than 1.
COT
Description Computes the cotangent for the angle of
Return Data Type
Example
COTH
Description Computes the hyperbolic cotangent for the angle of
Return Data Type
Example
CSC
Description Computes the cosecant of the input angle, which is in radians.
Return Data Type
Example
CSCH
Description Computes the hyperbolic cosecant of the input angle, which is in radians.
Return Data Type
Example
SEC
Description Computes the secant for the angle of
Return Data Type
Example
SECH
Description Computes the hyperbolic secant for the angle of
Return Data Type
Example
SIN
Description Computes the sine of X where X is specified in radians. Never fails.
SINH
Description Computes the hyperbolic sine of X where X is specified in radians. Generates an error if overflow occurs.
ASIN
Description Computes the principal value of the inverse sine of X. The return value is in the range [-π/2,π/2]. Generates an error if X is outside of the range [-1, 1].
ASINH
Description Computes the inverse hyperbolic sine of X. Does not fail.
TAN
Description Computes the tangent of X where X is specified in radians. Generates an error if overflow occurs.
TANH
Description Computes the hyperbolic tangent of X where X is specified in radians. Does not fail.
ATAN
Description Computes the principal value of the inverse tangent of X. The return value is in the range [-π/2,π/2]. Does not fail.
ATANH
Description Computes the inverse hyperbolic tangent of X. Generates an error if X is outside of the range [-1, 1].
ATAN2
Description Calculates the principal value of the inverse tangent of X/Y using the signs of the two arguments to determine the quadrant. The return value is in the range [-π,π].
CBRT
Description Computes the cube root of
Return Data Type
Example
RANGE_BUCKET
Description
Execution failure occurs when:
Parameters
Return Value
Examples In a table called
Navigation functionsThe following sections describe the navigation functions that BigQuery supports. Navigation functions are a subset window functions. To create a window function call and learn about the syntax for window functions, see Window function_calls. Navigation functions generally compute some For
all navigation functions, the result data type is the same type as FIRST_VALUE
Description Returns the value of the This function includes To learn more about the Supported Argument Types
Return Data Type Same type as Examples The following example computes the fastest time for each division.
LAST_VALUE
Description Returns the value of the This function includes To learn more about the Supported Argument Types
Return Data Type Same type as Examples The following example computes the slowest time for each division.
NTH_VALUE
Description Returns the value of This function includes To learn more about the Supported Argument Types
Return Data Type Same type as Examples
LEAD
Description Returns the value of the The optional To learn more about the Supported Argument Types
Return Data Type Same type as Examples The following example illustrates a basic use of the
This next example uses the optional
The following example replaces NULL values with a default value.
LAG
Description Returns the value of the The optional To learn more about the Supported Argument Types
Return Data Type Same type as Examples The following example illustrates a basic use of the
This next example uses the optional
The following example replaces NULL values with a default value.
PERCENTILE_CONT
Description Computes the specified percentile value for the value_expression, with linear interpolation. This function ignores NULL values if
To learn more about the Supported Argument Types
Return Data Type The return data type is determined by the argument types with the following table.
Examples The following example computes the value for some percentiles from a column of values while ignoring nulls.
The following example computes the value for some percentiles from a column of values while respecting nulls.
PERCENTILE_DISC
Description Computes the specified percentile value for a discrete This function ignores To learn more about the Supported Argument Types
Return Data Type Same type as Examples The following example computes the value for some percentiles from a column of values while ignoring nulls.
The following example computes the value for some percentiles from a column of values while respecting nulls.
Hash functionsFARM_FINGERPRINT
Description Computes the fingerprint of the Return type INT64 Examples
MD5
Description Computes the hash of the input using the MD5 algorithm. The input can either be This function returns 16 bytes. Return type
Example
SHA1
Description Computes the hash of the input using the SHA-1 algorithm. The input can either be This function returns 20 bytes. Return type
Example
SHA256
Description Computes the hash of the input using the SHA-256 algorithm. The input can either be This function returns 32 bytes. Return type
Example
SHA512
Description Computes the hash of the input using the SHA-512 algorithm. The input can either be This function returns 64 bytes. Return type
Example
String functionsThese string functions work on two different values: Functions that return position values, such as STRPOS, encode those positions as All string comparisons are done byte-by-byte, without regard to Unicode canonical equivalence. ASCII
Description Returns the ASCII code for the first character or byte in Return type
Examples
BYTE_LENGTH
Description Returns the length of the Return type
Examples
CHAR_LENGTH
Description Returns the length of the Return type
Examples
CHARACTER_LENGTH
Description Synonym for CHAR_LENGTH. Return type
Examples
CHR
Description Takes a Unicode
code point and returns the character that matches the code point. Each valid code point should fall within the range of [0, 0xD7FF] and [0xE000, 0x10FFFF]. Returns an empty string if the code point is To work with an array of Unicode code points, see
Return type
Examples
CODE_POINTS_TO_BYTES
Description Takes an array of extended ASCII code points
( To convert from Return type
Examples The following is a basic example using
The following example uses a rotate-by-13 places (ROT13) algorithm to encode a string.
CODE_POINTS_TO_STRING
Description Takes an array of Unicode code points ( To convert from a string to an array of code points, see TO_CODE_POINTS. Return type
Examples The following are basic examples using
The following example computes the frequency of letters in a set of words.
COLLATE
Takes a The collation specification defines how the resulting
Return type
Examples In this example, the weight of
In this example, the weight of
CONCAT
Description Concatenates one or more values into a single result. All values must be The function
returns Return type
Examples
CONTAINS_SUBSTR
Description Performs a normalized, case-insensitive search to see if a value exists in an expression. Returns The search value must be a The expression can be a column or table reference. A table reference is evaluated as a
When the expression is evaluated, the result is cast to a
You can perform a cross-field search on an expression that evaluates to a In a cross-field search, each field and
subfield is individually converted to a string and searched for the value. The function returns Before values are compared, they are
normalized and case folded with Return type
Examples The following query returns
The following query returns
The following query returns
The following query returns
The following query returns
The following query returns
The following query returns
In the following query, an error is thrown because the search value cannot be a literal
The following examples reference a table called
The following query searches across all columns of the
The following query searches the
The following queries search across all columns of the
ENDS_WITH
Description Takes two This function supports specifying collation. Return type
Examples
FORMAT
Description
Return type
Examples
The If custom formatting is necessary for a type, you must first format it using type-specific format functions, such as
Returns
Supported format specifiers
A format specifier adds formatting when casting a value to a string. It can optionally contain these sub-specifiers:
Additional information about format specifiers:
Format specifiers
*The specifiers The format specifier can optionally contain the sub-specifiers identified above in the specifier prototype. These sub-specifiers must comply with the following specifications. Flags
Flags may be specified in any order. Duplicate flags are not an error. When flags are not relevant for some element type, they are ignored. Width
Precision
%g and %G behaviorThe Let p stand for the specified precision (defaults to 6; 1 if the specified precision is less than 1). The input value is first converted to scientific notation with precision = (p - 1). If the resulting exponent part x is less than -4 or no less than p, the scientific notation with precision = (p - 1) is used; otherwise the decimal notation with precision = (p - 1 - x) is used. Unless %p and %P behaviorThe
%t and %T behaviorThe The The The
Error conditionsIf a format specifier is invalid, or is not compatible with the related argument type, or the wrong number or arguments are provided, then an error is produced. For example, the following
NULL argument handlingA The function generally produces a However, there are some exceptions: if the format specifier is %t or %T (both of which produce
Returns
Additional semantic rules
FROM_BASE32
Description Converts the base32-encoded input Return type
Example
FROM_BASE64
Description Converts the base64-encoded input There are several base64 encodings in common use that vary in exactly which alphabet of 65 ASCII characters are used to encode the 64 digits and padding. See RFC 4648 for details. This function expects the alphabet Return type
Example
To work with an encoding using a different base64 alphabet, you might need to compose
FROM_HEX
Description Converts a hexadecimal-encoded Return type
Example
INITCAP
Description Takes a
If Return type
Examples
INSTR
Description Returns the lowest 1-based index of If If This function supports specifying collation. Return type
Examples
LEFT
Description Returns a If Return type
Examples
LENGTH
Description Returns the length of the Return type
Examples
LPAD
Description Returns a The default value of Both If If This function returns an error if:
Return type
Examples
LOWER
Description For For Return type
Examples
LTRIM
Description Identical to TRIM, but only removes leading characters. Return type
Examples
NORMALIZE
Description Takes a string value and returns it as a normalized
string. If you do not provide a normalization mode, Normalization is used to ensure that two strings are equivalent. Normalization is often used in situations in which two strings render the same on the screen but have different Unicode code points.
Return type
Examples
The following example normalizes different space characters.
NORMALIZE_AND_CASEFOLD
Description Takes a string value and returns it as a normalized string. If you do not provide a normalization mode, Normalization is used to ensure that two strings are equivalent. Normalization is often used in situations in which two strings render the same on the screen but have different Unicode code points. Case folding is used for the caseless comparison of strings. If you need to compare strings and case should not be
considered, use
Return type
Examples
OCTET_LENGTH
Alias for REGEXP_CONTAINS
Description Returns If the You can search for a full match by using Return type
Examples
Description Returns the substring in If the regular expression contains a capturing group, the function returns the substring that is matched by that capturing group. If the expression does not contain a capturing group, the function returns the entire matching substring. If If Returns an error if:
Return type
Examples
Description Returns an array of all substrings of The Return type An Examples
REGEXP_INSTR
Description Returns the lowest 1-based index of a regular expression, If If You can optionally use Return type
Examples
REGEXP_REPLACE
Description Returns a You can use backslashed-escaped digits (\1 to \9) within the To add a backslash in your regular expression, you must first escape it. For example, The If the Return type
Examples
REGEXP_SUBSTR
Description Synonym for REGEXP_EXTRACT. Return type
Examples
REPLACE
Description Replaces all occurrences of This function supports specifying collation. Return type
Examples
REPEAT
Description Returns a
This function returns an error if the Return type
Examples
REVERSE
Description Returns the reverse of the input Return type
Examples
RIGHT
Description Returns a If Return type
Examples
RPAD
Description Returns a The default value of Both If If This function returns an error if:
Return type
Examples
RTRIM
Description Identical to TRIM, but only removes trailing characters. Return type
Examples
SAFE_CONVERT_BYTES_TO_STRING
Description Converts a sequence of Return type
Examples The following statement returns the Unicode replacement character, �.
SOUNDEX
Description Returns a SOUNDEX produces a phonetic representation of a string. It indexes words by sound, as pronounced in English. It is typically used to help determine whether two strings, such as the family names Levine and Lavine, or the words to and too, have similar English-language pronunciation. The result of the SOUNDEX consists of a letter followed by 3 digits.
Non-latin characters are ignored. If the remaining string is empty after removing non-Latin characters, an empty Return type
Examples
SPLIT
Description Splits For For Splitting on an
empty delimiter produces an array of UTF-8 characters for Splitting an empty This function supports specifying collation. Return type
Examples
STARTS_WITH
Description Takes two This function supports specifying collation. Return type
Examples
STRPOS
Description Takes two This function supports specifying collation. Return type
Examples
SUBSTR
Description Returns a substring of the supplied The
The
Return type
Examples
SUBSTRING
Alias for TO_BASE32
Description Converts a sequence of Return type
Example
TO_BASE64
Description Converts a sequence of There are several base64 encodings in common use that vary in exactly which alphabet of 65 ASCII characters are used to encode the 64 digits and padding. See RFC 4648 for details. This function adds padding and uses the alphabet Return type
Example
To work with an encoding using a different base64 alphabet, you might need to compose
TO_CODE_POINTS
Description Takes a value and returns an array of
To convert from an array of code points to a Return type
Examples The following example gets the code points for each element in an array of words.
The following example converts integer representations of
The following
example demonstrates the difference between a
Notice that the character, Ā, is represented as a two-byte Unicode sequence. As a result, the TO_HEX
Description Converts a sequence of Return type
Example
TRANSLATE
Description In
Return type
Examples
TRIM
Description Takes a If the value to trim is a If the
value to trim is Return type
Examples In the following example, all leading and trailing whitespace characters are removed from
In the following example, all leading and
trailing
In the following example, all leading and trailing
In the following example, examine how
In the following example,
all leading and trailing
UNICODE
Description Returns the Unicode code point for the first character in Return type
Examples
UPPER
Description For For Return type
Examples
JSON functionsBigQuery supports the following functions, which can retrieve and transform JSON data. Function overviewStandard JSON extraction functions (recommended)The following functions use double quotes to escape invalid JSONPath characters: This behavior is consistent with the ANSI standard.
The following functions use single quotes and brackets to escape invalid JSONPath characters: While these functions are supported by BigQuery, we recommend using the functions in the previous table.
Other JSON functions
Description Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.
Return type
Examples In the following example, JSON data is extracted and returned as JSON.
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
JSON_QUERY
Description Extracts a JSON value, such as an array or object, or a JSON scalar value, such as a string, number, or boolean. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
Return type
Examples In the following example, JSON data is extracted and returned as JSON.
In the following examples, JSON data is extracted and returned as JSON-formatted strings.
Description Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.
Return type
Examples In the following example,
The following example compares how results are returned for the
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets,
JSON_VALUE
Description Extracts a scalar value and then returns it as a string. A scalar value can represent a string, number, or boolean. Removes the outermost quotes and unescapes the return values. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
Return type
Examples In the following example, JSON data is extracted and returned as a scalar value.
The following example compares how results are returned for the
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes. For example:
Description Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using single quotes and brackets.
Return type
Examples This extracts items in JSON to an array of
This extracts the items in a JSON-formatted string to a string array:
This extracts a string array and converts it to an integer array:
This extracts string values in a JSON-formatted string to an array:
This extracts only the items in the
These are equivalent:
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets,
The following examples explore how invalid requests and empty arrays are handled:
JSON_QUERY_ARRAY
Description Extracts an array of JSON values, such as arrays or objects, and JSON scalar values, such as strings, numbers, and booleans. If a JSON key uses invalid JSONPath characters, then you can escape those characters using double quotes.
Return type
Examples This extracts items in JSON to an array of
This extracts the items in a JSON-formatted string to a string array:
This extracts a string array and converts it to an integer array:
This extracts string values in a JSON-formatted string to an array:
This extracts only the items
in the
These are equivalent:
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes:
The following examples show how invalid requests and empty arrays are handled:
Description Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets.
Caveats:
Return type
Examples This extracts items in JSON to a string array:
The following example
compares how results are returned for the
This extracts the items in a JSON-formatted string to a string array:
This extracts a string array and converts it to an integer array:
These are equivalent:
In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets:
The following examples explore how invalid requests and empty arrays are handled:
JSON_VALUE_ARRAY
Description Extracts an array of scalar values and returns an array of string-formatted scalar values. A scalar value can represent a string, number, or boolean. If a JSON key uses invalid JSONPath characters, you can escape those characters using double quotes.
Caveats:
Return type
Examples This extracts items in JSON to a string array:
The following example compares how results are returned for the
This extracts the items in a JSON-formatted string to a string array:
This extracts a string array and converts it to an integer array:
These are equivalent:
In cases where a JSON
key uses invalid JSONPath characters, you can escape those characters using double quotes:
The following examples explore how invalid requests and empty arrays are handled:
PARSE_JSON
Description Takes a SQL This function supports an optional mandatory-named
argument called
If Numbers from the following domains can be stored in JSON without loss of precision:
Return type
Examples In the following example, a JSON-formatted string is converted to
The following queries fail because:
The following query rounds the number to a number that can be stored in JSON.
TO_JSON
Description Takes a SQL value and returns a JSON value. The value must be a supported BigQuery data type. You can review the BigQuery data types that this function supports and their JSON encodings here. This function supports an optional mandatory-named argument called
The following numerical data types are affected by the
If one of these numerical data types appears in a container data type such as an Return type A JSON value Examples In the following example, the query converts rows in a table to JSON values.
In the following example, the query returns a large numerical value as a JSON string.
In the following example, both queries return a large numerical value as a JSON number.
In the following example, only large numeric values are converted to JSON strings.
In this example, the values
TO_JSON_STRING
Description Takes a SQL value and returns a JSON-formatted string representation of the value. The value must be a supported BigQuery data type. You can review the BigQuery data types that this function supports and their JSON encodings here. This function supports an optional boolean parameter called Return type A JSON-formatted Examples Convert rows in a table to JSON-formatted strings.
Convert rows in a table to JSON-formatted strings that are easy to read.
STRING
Description Takes a JSON expression, extracts a JSON string, and returns that value as a SQL
Return type
Examples
The following examples show how invalid requests are handled:
BOOL
Description Takes a JSON expression, extracts a JSON boolean, and returns that value as a SQL
Return type
Examples
The following examples show how invalid requests are handled:
INT64
Description Takes a JSON expression, extracts
a JSON number and returns that value as a SQL
Return type
Examples
The following examples show how invalid requests are handled:
FLOAT64
Description Takes a JSON expression, extracts a JSON number and returns that value as a SQL
This function supports an optional mandatory-named argument called This argument accepts one of the two case-sensitive values:
Return type
Examples
The following examples show how invalid requests are handled:
JSON_TYPE
Description Takes a JSON expression and returns the type of the outermost JSON value as a SQL
If the expression is SQL
Return type
Examples
JSON encodingsThe following table includes common encodings that are used when a SQL value is encoded as JSON value with the
JSONPath formatWith the JSONPath format, you can identify the values you want to obtain from a JSON-formatted string. The JSONPath format supports these operators:
If a key in a JSON functions contains a JSON format operator, refer to each JSON function for how to escape them. A JSON function returns Array functionsARRAY
Description The If If Constraints
Return type ARRAY Examples
To construct an
Similarly, to construct an
ARRAY_CONCAT
Description Concatenates one or more arrays with the same element type into a single array. The function returns Return type ARRAY Examples
ARRAY_LENGTH
Description Returns the size of the array. Returns 0 for an empty array. Returns Return type INT64 Examples
ARRAY_TO_STRING
Description Returns a concatenation of the elements in If the If the Examples
GENERATE_ARRAY
Description Returns an array of values. The The
The This function returns an error if If any argument is Return Data Type ARRAY Examples The following returns an array of integers, with a default step of 1.
The following returns an array using a user-specified step size.
The following returns an array using a negative value,
The following returns an array using the same value for the
The following returns an empty array, because the
The
following returns a
The following returns multiple arrays.
GENERATE_DATE_ARRAY
Description Returns an array of dates. The The
The This function returns an error if Return Data Type An ARRAY containing 0 or more DATE values. Examples The following returns an array of dates, with a default step of 1.
The following returns an array using a user-specified step size.
The following returns an array using a negative value,
The following returns an array using the same value for the
The following returns an empty array, because the
The following returns a
The following returns an array of dates, using MONTH as the
The following uses non-constant dates to generate an array.
GENERATE_TIMESTAMP_ARRAY
Description Returns an The
The Return Data Type An Examples The following example returns an
The following example returns an
The following example returns an
The following example returns an
The following example returns an empty
The following example returns a null
The following example generates
ARRAY_REVERSE
Description Returns the input ARRAY with elements in reverse order. Return type ARRAY Examples
OFFSET and ORDINALFor information about using Date functionsBigQuery supports the following CURRENT_DATE
Description Returns the current date as of the specified or default time zone. Parentheses are optional when called with no arguments. This function supports an optional If the Return Data Type DATE Example
When a column named
Description Returns the
value corresponding to the specified date part. The
Return Data Type INT64 Examples In the following example,
In the following example,
In the following example,
DATE
Description
Return Data Type DATE Example
DATE_ADD
Description Adds a specified time interval to a DATE.
Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the resulting date is the last date of that month. Return Data Type DATE Example
DATE_SUB
Description Subtracts a specified time interval from a DATE.
Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the resulting date is the last date of that month. Return Data Type DATE Example
DATE_DIFF
Description Returns the whole number of specified
Return Data Type INT64 Example
The example above shows the result of The following example shows the result of
The following example shows the result of
DATE_TRUNC
Description Truncates a
Return Data Type DATE Examples
In the following example, the original date falls on a Sunday. Because the
In the following example, the original
DATE_FROM_UNIX_DATE
Description Interprets Return Data Type DATE Example
FORMAT_DATE
Description Formats the See Supported Format Elements For DATE for a list of format elements that this function supports. Return Data Type STRING Examples
LAST_DAY
Description Returns the last day from a date expression. This is commonly used to return the last day of the month. You can optionally specify the date part for which the last day is returned. If this parameter is not used, the default value is
Return Data Type
Example These both return the last day of the month:
This returns the last day of the year:
This returns the last day of the week for a week that starts on a Sunday:
This returns the last day of the week for a week that starts on a Monday:
PARSE_DATE
Description Converts a string representation of date to a
When using
Return Data Type DATE Examples This example converts a
This example converts a
UNIX_DATE
Description Returns the number of days since 1970-01-01. Return Data Type INT64 Example
Datetime functionsBigQuery supports the following All outputs are automatically formatted as per ISO 8601, separating date and time with a CURRENT_DATETIME
Description Returns the current time as a This function supports an optional Return Data Type
Example
When a column named
DATETIME
Description
Return Data Type
Example
Description Returns a value that corresponds to the specified Allowed
Returned values
truncate lower order time periods. For example, when extracting seconds, Return Data Type
Examples In the following example,
In the
following example,
In the following example,
DATETIME_ADD
Description Adds
Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original DATETIME's day, then the result day is the last day of the new month. Return Data Type
Example
DATETIME_SUB
Description Subtracts
Special handling is required for Return Data Type
Example
DATETIME_DIFF
Description Returns the whole number of specified
Return Data Type
Example
The example above shows the result of The following example shows the result of
The following example shows the result of
DATETIME_TRUNC
Description Truncates a
Return Data Type
Examples
In the following example, the original
In the following example, the original
FORMAT_DATETIME
Description Formats a Return Data Type
Examples
LAST_DAY
Description Returns the last day from a datetime expression that contains the date. This is commonly used to return the last day of the month. You can optionally specify the date part for which the last day is returned. If this parameter is not used, the default value is
Return Data Type
Example These both return the last day of the month:
This returns the last day of the year:
This returns the last day of the week for a week that starts on a Sunday:
This returns the last day of the week for a week that starts on a Monday:
PARSE_DATETIME
Description Converts a
string representation of a datetime to a
The format string fully supports most format elements, except for
Return Data Type
Examples The following examples parse a
The following example parses a
Time functionsBigQuery supports the following CURRENT_TIME
Description Returns the current time as a This function supports an optional Return Data Type
Example
When a column named
TIME
Description
Return Data Type
Example
Description Returns a value that corresponds to the specified Allowed
Returned values truncate lower order time periods. For example, when extracting seconds,
Return Data Type
Example In the following example,
TIME_ADD
Description Adds
This function automatically adjusts when values fall outside of the 00:00:00 to 24:00:00 boundary. For example, if you add an hour to Return Data Types
Example
TIME_SUB
Description Subtracts
This function automatically adjusts when values fall outside of the 00:00:00 to 24:00:00 boundary. For example, if you subtract an hour from Return Data Type
Example
TIME_DIFF
Description Returns the whole number of specified
Return Data Type
Example
TIME_TRUNC
Description Truncates a
Return Data Type
Example
FORMAT_TIME
Description Formats a Return Data Type
Example
PARSE_TIME
Description Converts a
string representation of time to a
The format string fully supports most format elements except for When using
Return Data Type
Example
Timestamp functionsBigQuery supports several IMPORTANT: Before working with these functions, you need to understand the difference between the formats in which timestamps are stored and displayed, and how time zones are used for the conversion between these formats. To learn more, see How time zones work with timestamp functions. NOTE: These functions return a runtime error if overflow occurs; result values are bounded by the defined date and timestamp min/max values. CURRENT_TIMESTAMP
Description
This function handles leap seconds by smearing them across a window of 20 hours around the inserted leap second. Supported Input Types Not applicable Result Data Type
Examples
When a column named
Description Returns a value that corresponds to the specified Allowed
Returned values truncate lower order time periods. For example, when extracting seconds, Return Data Type
Examples In the following example,
In the following example,
In the following example,
STRING
Description Converts a Return Data Type
Example
TIMESTAMP
Description
This function supports an optional parameter to specify a time zone. If no time zone is specified, the default time zone, UTC, is used. Return Data Type
Examples
TIMESTAMP_ADD
Description Adds
Return Data Types
Example
TIMESTAMP_SUB
Description Subtracts
Return Data Type
Example
TIMESTAMP_DIFF
Description Returns the whole number of specified
Return Data Type
Example
In the following example, the first timestamp occurs before the second timestamp, resulting in a negative output.
In this example, the result is 0 because only the number
of whole specified
TIMESTAMP_TRUNC
Description Truncates a
Use this parameter if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation. When truncating a Return Data Type
Examples
In the following example,
In the following example, the original
FORMAT_TIMESTAMP
Description Formats a timestamp according to the specified See Supported Format Elements For TIMESTAMP for a list of format elements that this function supports. Return Data Type
Example
PARSE_TIMESTAMP
Description Converts a string representation of a timestamp to a
The format string fully supports most format elements, except for When using
Return Data Type
Example
TIMESTAMP_SECONDS
Description Interprets Return Data Type
Example
TIMESTAMP_MILLIS
Description Interprets Return Data Type
Example
TIMESTAMP_MICROS
Description Interprets Return Data Type
Example
UNIX_SECONDS
Description Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision. Return Data Type
Example
UNIX_MILLIS
Description Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision. Return Data Type
Example
UNIX_MICROS
Description Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision. Return Data Type
Example
How time zones work with timestamp functionsA timestamp represents an absolute point in time, independent of any time zone. However, when a timestamp value is displayed, it is usually converted to a human-readable format consisting of a civil date and time (YYYY-MM-DD HH:MM:SS) and a time zone. Note that this is not the internal representation of the timestamp; it is only a human-understandable way to describe the point in time that the timestamp represents. Some timestamp functions have a time zone argument. A time zone is needed to convert between civil time (YYYY-MM-DD HH:MM:SS) and absolute time (timestamps). A function like Certain date and timestamp functions allow you to override the default time zone and specify a different one. You can specify a time zone by either supplying the
time zone name (for example, To learn more about how time zones work with timestamps, see Time zones. Interval functionsBigQuery supports the following MAKE_INTERVAL
Description Constructs an Return Data Type
Example
Description Returns the value corresponding
to the specified date part. The Return Data Type
Example
JUSTIFY_DAYS
Description Normalizes the day part of the interval to the range from -29 to 29 by incrementing/decrementing the month or year part of the interval. Return Data Type
Example
JUSTIFY_HOURS
Description Normalizes the time part of the interval to the range from -23:59:59.999999 to 23:59:59.999999 by incrementing/decrementing the day part of the interval. Return Data Type
Example
JUSTIFY_INTERVAL
Description Normalizes the days and time parts of the interval. Return Data Type
Example
Geography functionsThe geography functions operate on or generate BigQuery All BigQuery geography functions return CategoriesThe geography functions are grouped into the following categories based on their behavior:
S2_CELLIDFROMPOINT
Description Returns the S2 cell ID covering a point
This is advanced functionality for interoperability with systems utilizing the S2 Geometry Library. Constraints
Return type
Example
S2_COVERINGCELLIDS
Description Returns an array of S2 cell ids that cover the input This is advanced functionality for interoperability with systems utilizing the S2 Geometry Library. Constraints
Return type
Example
ST_ANGLE
Description Takes three point
Return type
Example
ST_AREA
Description Returns the area in square meters covered by the polygons in the input If The optional The Return type
ST_ASBINARY
Description Returns the WKB representation of an input See Return type
ST_ASGEOJSON
Description Returns the RFC 7946 compliant GeoJSON representation of the input A BigQuery See Return type
ST_ASTEXT
Description Returns the
WKT representation of an input See Return type
ST_AZIMUTH
Description Takes two point The positive angle is measured clockwise on the surface of a sphere. For example, the azimuth for a line segment:
Return type
Example
ST_BOUNDARY
Description Returns a single The boundary of each component of a
Return type
ST_BOUNDINGBOX
Description Returns a Caveats:
Return type
Bounding box parts:
Example
See ST_BUFFER
Description Returns a
Return type
Example The following example shows the result of
ST_BUFFERWITHTOLERANCE
Returns a
Return type
Example The following example shows the results of
ST_CENTROID
Description Returns the centroid of the input The centroid of a
If the input Constraints In the unlikely event that the centroid of a Return type
ST_CENTROID_AGG
Description Computes the centroid of the set of input The centroid over the set of input
See Return type
Example The following queries compute the aggregate centroid over a set of
ST_CLOSESTPOINT
Description Returns a If either of the input The optional The Return type
ST_CLUSTERDBSCAN
Performs DBSCAN clustering on a column of geographies. Returns a 0-based cluster number. To learn more about the Input parameters
Geography types and the DBSCAN algorithm The DBSCAN algorithm identifies high-density clusters of data and marks
outliers in low-density areas of noise. Geographies passed in through
Constraints
Return type
Examples This example performs DBSCAN clustering with a radius of 100,000 meters with a
ST_CONTAINS
Description Returns NOTE: A Return type
Example The following query tests whether the polygon
ST_CONVEXHULL
Description Returns the convex hull for the input In most cases, the convex hull consists of a single polygon. Notable edge cases include the following:
Return type
Examples The convex hull returned by
ST_COVEREDBY
Description Returns Given two Return type
ST_COVERS
Description Returns Return type
Example The following query tests whether the polygon
ST_DIFFERENCE
Description Returns a If Constraints The underlying geometric objects that a BigQuery Return type
Example The following query illustrates the diffence between
ST_DIMENSION
Description Returns the dimension of the
highest-dimensional element in the input The dimension of each possible element is as follows:
If the input Return type
ST_DISJOINT
Description Returns
Return type
ST_DISTANCE
Description Returns
the shortest distance in meters between two non-empty If either of the input The optional The Return type
ST_DUMP
Description Returns an If Return Type
Examples The following example shows how
The following example shows how
ST_DWITHIN
Description Returns The optional The Return type
ST_ENDPOINT
Description Returns the last point of a linestring geography as a point geography. Returns an error if the input is not a linestring or if the input is empty. Use the Return Type Point Example
ST_EQUALS
Description Returns
Therefore, two Constraints
Return type
ST_EXTENT
Description Returns a Caveats:
Return type
Bounding box parts:
Example
See ST_EXTERIORRING
Description Returns a linestring geography that corresponds to the outermost ring of a polygon geography.
Use the Return type
Examples
ST_GEOGFROM
Description Converts an expression for a If
If If Return type
Examples This takes a WKT-formatted string and returns a
This takes a WKB-formatted hexadecimal-encoded string and returns a
This takes WKB-formatted bytes and returns a
This takes a GEOJSON-formatted string and returns a
ST_GEOGFROMGEOJSON
Description Returns a
If the parameter A BigQuery See Constraints The input is subject to the following constraints:
Return type
ST_GEOGFROMTEXT
Signature 1
Description Returns a This function supports an optional parameter of type To format Constraints
Return type
Example The following query reads the WKT string
Signature 2
Description Returns a This function supports three optional parameters of type If the If the parameter If the parameter To format Constraints
Example The following query reads the WKT string
The following query converts a WKT string with an invalid polygon to
ST_GEOGFROMWKB
Description Converts an expression for a hexadecimal-text To format Constraints All input edges are assumed to be spherical geodesics, and not planar straight lines. For reading data in a planar projection, consider using Return type
ST_GEOGPOINT
Description Creates a Constraints
Return type
ST_GEOGPOINTFROMGEOHASH
Description Returns a Return type
ST_GEOHASH
Description Takes a single-point
Return type
Example Returns a GeoHash of the Seattle Center with 10 characters of precision.
ST_GEOMETRYTYPE
Description Returns the Open Geospatial Consortium (OGC) geometry type that describes the input
Return type
Example The following example shows how
ST_INTERIORRINGS
Description Returns an array of linestring geographies that corresponds to the interior rings of a polygon geography. Each interior ring is the border of a hole within the input polygon.
Use the Return type
Examples
ST_INTERSECTION
Description Returns a If the two input See ST_INTERSECTS, ST_DISJOINT for related predicate functions. Return type
ST_INTERSECTS
Description Returns If Return type
ST_INTERSECTSBOX
Description Returns Specify all longitude and latitude arguments in degrees. Constraints The input arguments are subject to the following constraints:
Return type
Example
ST_ISCOLLECTION
Description Returns An empty Return type
ST_ISEMPTY
Description Returns NOTE: A BigQuery empty Return type
ST_LENGTH
Description Returns the total length in meters of the lines in the input If The optional The Return type
ST_MAKELINE
Description Creates a
For the first variant of Constraints Every edge must span strictly less than 180 degrees. NOTE: BigQuery's snapping process may discard sufficiently short edges and snap the two endpoints together. For instance, if two input Return type
ST_MAKEPOLYGON
Description Creates a
For the first variant of NOTE: Constraints Together, the input rings must form a valid polygon:
Every edge must span strictly less than 180 degrees. Each polygon ring divides the sphere into two regions. The first input linesting to NOTE: BigQuery's snapping process may discard sufficiently short edges and snap the two endpoints together. Hence, when vertices are snapped together, it is possible that a polygon hole that is sufficiently small may disappear, or the
output Return type
ST_MAKEPOLYGONORIENTED
Description Like This variant of the polygon constructor is more flexible since If the input NOTE: The
input argument for Constraints Together, the input rings must form a valid polygon:
Every edge must span strictly less than 180 degrees.
NOTE: Due to BigQuery's snapping process, edges with a sufficiently short length will be discarded and the two endpoints will be snapped to a single point. Therefore, it is possible that vertices in a linestring may be snapped together such that one or more edge
disappears. Hence, it is possible that a polygon hole that is sufficiently small may disappear, or the resulting Return type
ST_MAXDISTANCE
Returns the longest distance in meters between two non-empty If either of the input The optional The Return type
ST_NPOINTS
Description An alias of ST_NUMPOINTS. ST_NUMGEOMETRIES
Description Returns
the number of geometries in the input Return type
Example The following example computes
ST_NUMPOINTS
Description Returns the number of vertices in the input NOTE: The first and last vertex of a polygon ring are counted as distinct vertices. Return type
ST_PERIMETER
Description Returns the length in meters of the boundary of the polygons in the input If The optional The Return type
ST_POINTN
Description Returns the Nth point of a linestring geography as a point geography, where N is the index. The index is 1-based. Negative values are counted backwards from the end of the linestring, so that -1 is the last point. Returns an error if the input is not a
linestring, if the input is empty, or if there is no vertex at the given index. Use the Return Type Point Example The following example uses
ST_SIMPLIFY
Description Returns a simplified version of Note that Constraints For
Return type
Examples The following example shows how
The following example illustrates how the result of
ST_SNAPTOGRID
Description Returns the input Constraints Arbitrary grid sizes are not supported. The Return type
ST_STARTPOINT
Description Returns the first point of a linestring geography as a point geography. Returns an error if the input is not a linestring or if the input is empty. Use the
Return Type Point Example
ST_TOUCHES
Description Returns
Return type
ST_UNION
Description Returns a
For the first variant of See Return type
ST_UNION_AGG
Description Returns a
See Return type
ST_WITHIN
Description Returns Given two geographies Return type
ST_X
Description Returns the longitude in degrees of the single-point input For any input Return type
Example The following example uses
ST_Y
Description Returns the latitude in degrees of the single-point input For any input Return type
Example See
Security functionsBigQuery supports the following security functions. SESSION_USER
Description Returns the email address of the user that is running the query. Return Data Type STRING Example
Utility functionsBigQuery supports the following utility functions. GENERATE_UUID
Description Returns a random universally unique identifier (UUID) as a Return Data Type STRING Example The following query generates a random UUID.
Net functionsNET.IP_FROM_STRING
Description Converts an IPv4 or IPv6 address from text (STRING) format to binary (BYTES) format in network byte order. This function supports the following formats for
This function
does not support CIDR notation, such as If this function receives a Return Data Type BYTES Example
NET.SAFE_IP_FROM_STRING
Description Similar to Return Data Type BYTES Example
NET.IP_TO_STRING
Description Converts an IPv4 or IPv6 address from binary (BYTES) format in network byte order to text (STRING) format. If the input is 4 bytes, this function returns an IPv4 address as a STRING. If the input is 16 bytes, it returns an IPv6 address as a STRING. If this function receives a Return Data Type STRING Example
NET.IP_NET_MASK
Description Returns a network mask: a byte sequence with length equal to Return Data Type BYTES Example
NET.IP_TRUNC
Description Takes This function throws an error if Return Data Type BYTES Example
NET.IPV4_FROM_INT64
Description Converts an IPv4 address from integer format to binary (BYTES) format in network byte order. In the integer input, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, This function
checks that either all the most significant 32 bits are 0, or all the most significant 33 bits are 1 (sign-extended from a 32-bit integer). In other words, the input should be in the range This function does not support IPv6. Return Data Type BYTES Example
NET.IPV4_TO_INT64
Description Converts an IPv4 address from binary (BYTES) format in network byte order to integer format. In the integer output, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, If the input length is not 4, this function throws an error. This function does not support IPv6. Return Data Type INT64 Example
NET.HOST
Description Takes a URL as a STRING and returns the host as a STRING. For best results, URL values should comply with the format as defined by RFC 3986. If the URL value does not comply with RFC 3986 formatting, this function makes a best effort to parse the input and return a relevant result. If the function cannot parse the input, it returns NULL. Note: The function does not perform any normalization. Return Data Type STRING Example
NET.PUBLIC_SUFFIX
Description Takes a URL as a STRING and returns the public suffix (such as This function returns NULL if any of the following is true:
Before looking up the public suffix, this function temporarily normalizes the host by converting upper case English letters to lower case and encoding all non-ASCII characters with Punycode. The function then returns the public suffix as part of the original host instead of the normalized host. Note: The function does not perform Unicode normalization. Note: The public suffix data at publicsuffix.org also contains private domains. This function ignores the private domains. Note: The public suffix data may change over time. Consequently, input that produces a NULL result now may produce a non-NULL value in the future. Return Data Type STRING Example
NET.REG_DOMAIN
Description Takes a URL as a STRING and returns the registered or registerable domain (the public suffix plus one preceding label), as a STRING. For best results, URL values should comply with the format as defined by RFC 3986. If the URL value does not comply with RFC 3986 formatting, this function makes a best effort to parse the input and return a relevant result. This function returns NULL if any of the following is true:
Before looking up the public suffix, this function temporarily normalizes the host by converting upper case English letters to lowercase and encoding all non-ASCII characters with Punycode. The function then returns the registered or registerable domain as part of the original host instead of the normalized host. Note: The function does not perform Unicode normalization. Note: The public suffix data at publicsuffix.org also contains private domains. This function does not treat a private domain as a public suffix. For example, if "us.com" is a private domain in the public suffix data, NET.REG_DOMAIN("foo.us.com") returns "us.com" (the public suffix "com" plus the preceding label "us") rather than "foo.us.com" (the private domain "us.com" plus the preceding label "foo"). Note: The public suffix data may change over time. Consequently, input that produces a NULL result now may produce a non-NULL value in the future. Return Data Type STRING Example
Debugging functionsBigQuery supports the following debugging functions. ERROR
Description Returns an error. The BigQuery treats Return Data Type BigQuery infers the return type in context. Examples In the following example, the query returns an error message if the value of the row does not match one of two defined values.
In the following example, BigQuery may evaluate the
In the next example, the
AEAD encryption functionsThe following sections describe the AEAD encryption functions that BigQuery supports. For a description of how the AEAD encryption functions work, see AEAD encryption concepts. KEYS.NEW_KEYSET
Description Returns a serialized keyset containing a new key based on
Return Data Type
Example The following query creates a keyset for each row in
KEYS.ADD_KEY_FROM_RAW_BYTES
Description Returns a serialized keyset as The primary cryptographic key remains the same as in
Return Data Type
Example The following query creates a table of customer IDs along with raw key bytes, called
The output keysets each contain two things: the primary cryptographic key created using
AEAD.DECRYPT_BYTES
Description Uses the matching key from
If
Return Data Type
Example This example creates a table of unique IDs with
associated plaintext values and keysets. Then it uses these keysets to encrypt the plaintext values as The following statement creates a table
The following statement creates a table
The following query uses the keysets in the
AEAD.DECRYPT_STRING
Description Like
Return Data Type
AEAD.ENCRYPT
Description Encrypts
The output is ciphertext Return Data Type
Example The following query uses the keysets for each
DETERMINISTIC_DECRYPT_BYTES
Description Uses the matching key from
The ciphertext must follow Tink's
wire format. The first byte of
Return Data Type
Example This example creates a table of unique IDs with associated plaintext values and keysets. Then it uses these keysets to encrypt the plaintext values as The following statement creates a table
The following statement creates a table
The following query uses the keysets in the
DETERMINISTIC_DECRYPT_STRING
Description Like Return Data Type
DETERMINISTIC_ENCRYPT
Description Encrypts
The output is ciphertext Return Data Type
Example The following query uses the keysets for each
KEYS.KEYSET_CHAIN
Description Can be
used in place of the
Both Return Data Type
Example This example creates a table of example data, then shows how to encrypt that data using a wrapped (encrypted) keyset. Finally it shows how to query the encrypted version of the data. The following statement creates a table
The following statement creates a table
The following query uses the first_level_keyset to decrypt data in the
The previous two steps also work with the The following statement creates a table
The following query uses
the first_level_keyset to decrypt data in the
KEYS.KEYSET_FROM_JSON
Description Returns the input Return Data Type
Example
The following query creates a new keyset from a JSON-formatted
This returns the
KEYS.KEYSET_TO_JSON
Description Returns a JSON Return Data Type
Example The following query returns a new
The result is a
KEYS.ROTATE_KEYSET
Description Adds a new key to The old primary cryptographic key from the input The new Return Data Type
Example The following statement creates a table containing a column of unique
KEYS.KEYSET_LENGTH
Description Returns the number of keys in the provided keyset. Return Data Type
Example This example references a JSON-formatted STRING called
The following query converts
What is a named list of data items that all have the same data type?An array is a collection of data items, all of the same type, accessed using a common name.
When a method returns an array reference you include ____ with the return type in the method header A B C D?When returning an array reference, square brackets are included with the return type in the method header.
What is the array name?Array name is a type of name or a type of any element name that is share by all elements of an array but its indexes are different. Array name handle as a constant pointer, it can never change during execution of a program. Array name is also used to reach its all element.
Is the technique of using the same method name to indicate different implementations?Polymorphism - "many forms" using the same method name to indicate different implementations. b. When you create a method in the child class that has the same name and parameter list as a method in its parent class, you override the method in the parent class.
|