Google BigQuery CAST Functions 101: Syntax and Usage Simplified

The Structured Query Language (SQL) powers Google BigQuery’s query system – SQL is made unequivocally for database management systems and is especially helpful in taking care of structured data. Other than the adaptability and performance, what makes Google BigQuery so well-known is its convenience. As Google BigQuery utilizes SQL as its query language, which is used by many popular databases and warehouse frameworks, it gets easier for data practitioners.

In this article, you will get to know about Google BigQuery CAST Functions, CAST functions key types, usage, and example. Google BigQuery function is one of the system syntaxes used to fetch data from a query table for analysis. A function syntax is a predefined set of clauses that accomplish a complex action. For instance, COUNT, SUM, and so on are some examples of the function syntax.

What is Google BigQuery CAST Function?

The CAST functions permit you to change between several Data Types in BigQuery. They are beneficial for guaranteeing that arguments in a comparison function have a similar data type. With the scalability of Google Cloud, you can easily cast BigQuery across vast volumes of data for lightning-fast insights. In one of our previous posts, we talked about BigQuery data types. BigQuery supports various data types. Each data type has several functions and operations that apply to them with restricted procedures. 

Example of a CAST Function syntax:

CAST (expr AS typename)
Or  
CAST(expression AS output_data_type)

The type name or data type in this instance are:

INT64, NUMERIC, BIGNUMERIC, FLOAT64, BOOL, STRING, BYTES, DATE, DATETIME, TIME, TIMESTAMP, ARRAY, STRUCT.

You can learn more about these data types in the post above. 

For instance, assuming you need to form a sentence, so you must first change overall data (counting numbers) into a STRING data type. Using various data types isn’t suggested and can bring about a syntax error. 

Examples of CAST Functions and their descriptions

Functions	Description
BOOLEAN( <numeric_expr> )	Returns true if <numeric_expr> is not 0 and not NULL.Returns false if <numeric_expr> is 0. Returns NULL if <numeric_expr> is NULL.
FLOAT( expr )	Returns expr as a double. The expr can be a string like ‘45.78’, but the function returns NULL for non-numeric values.
HEX_STRING( numeric_expr )	Returns numeric_expr as a hexadecimal string.
INTEGER( expr )	Returns expr as a 64-bit integer. This function expects an integer string, such as ’45’, and returns NULL for non-integer values.
STRING( numeric_expr )	Returns numeric_expr as a string.

Casting functions between data types that don’t effectively map from the first value to the target domain creates runtime errors. For instance, casting BYTES to STRING where the byte sequence isn’t legitimate, UTF-8 results in a runtime error. A few projects can incorporate a format clause, which gives directions to how to lead the CAST.

For instance, you could instruct a CAST to change a sequence of bytes over to a BASE64-encoded string rather than a UTF-8-encoded string. The design of the format clause is extraordinary to each type of CAST, and more data is accessible in the segment for that CAST.

Google BigQuery CAST Functions

In Google BigQuery, the CAST function allows you to convert between various Data Types. The flexibility of the cast in BigQuery enhances the efficiency of data processing. Below are some of the commonly used CAST functions:

• CAST AS ARRAY
• CAST AS BIGNUMERIC
• CAST AS BOOL
• CAST AS BYTES
• CAST AS DATE
• CAST AS DATETIME
• CAST AS FLOAT64
• CAST AS INT64
• CAST AS INTERVAL
• CAST AS Numeric
• CAST AS STRING
• CAST AS STRUCT
• CAST AS TIME
• CAST AS TIMESTAMP
• PARSE_BIGNUMERIC
• PARSE_NUMERIC
• SAFE_CAST

1) CAST AS ARRAY

You can cast ARRAY in BigQuery. It can be used to represent the data type called ARRAY.

Syntax:

CAST(expression AS ARRAY<element_type>)

However, to cast from array to array, both element types must be the same array type.

Best Practices: High Performance ETL to BigQuery

Download for Free

2) CAST AS BIGNUMERIC

Another data type that can be on Google BigQuery is BIGNUMERIC. It can be used to call the  following data types expressions:

• BIGNUMERIC
• FLOAT64
• INT64
• NUMERIC
• STRING

Syntax:

CAST(expression AS BIGNUMERIC)

Example:

SELECT
  str_number,
  CAST(TRIM(str_number) AS INT64) AS number
  FROM
  (
    SELECT
      '3' AS str_number
    UNION ALL
  ( SELECT
      '8' AS str_number)
    UNION ALL
  ( SELECT
      '23' AS str_number)
  ) AS table_3

3) CAST AS BOOL

The BOOL data type can also be cast on Google BigQuery as BOOL. It can be used to represent the following data types expressions:

• BOOL
• INT64
• STRING

Syntax:

CAST(expression AS BOOL)

Example:

SELECT
  CAST(0 AS BOOL) AS zero_to_bool,
  CAST(5 AS BOOL) AS five_to_bool
SELECT
  SAFE_CAST('true' AS BOOL) AS true_to_bool,
  SAFE_CAST('false' AS BOOL) AS false_to_bool,
  SAFE_CAST('apples' AS BOOL) AS apples_to_bool

4) CAST AS BYTES

The bytes data type can also be cast on Google BigQuery as BYTES. It can be used to represent the following data types expression:

• BYTES
• STRING

Syntax:

CAST(expression AS BYTES [format_clause])

5) CAST AS DATE

The date data type can also be cast on Google BigQuery as DATE. It can be used to represent the following data types expressions:

• DATETIME
• STRING
• TIME
• TIMESTAMP

Syntax:

CAST(expression AS DATE [format_clause])

6) CAST AS DATETIME

The DATETIME data type can also be cast on Google BigQuery as DATETIME. It can be used to represent the following data types expressions:

• DATETIME
• STRING
• TIME
• TIMESTAMP

Syntax:

CAST(expression AS DATETIME [format_clause])

Example:

SELECT
  CAST('2021-19-20 12:52:01' AS DATETIME) AS str_to_datetime

7) CAST AS FLOAT64

The float64 data type can also be cast on Google BigQuery as FLOAT64. It can be used to represent the following data types expressions:

• BIGNUMERIC
• FLOAT64
• INT64
• NUMERIC
• STRING

Syntax:

CAST(expression AS FLOAT64)

8) CAST AS INT64

The int64 data type can also be cast on Google BigQuery as INT64. It can be used to represent the following data types expressions:

• BIGNUMERIC
• BOOL
• FLOAT64
• INT64
• NUMERIC
• STRING

Syntax:

CAST(expression AS INT64)

Examples: 

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

9) CAST AS INTERVAL

The interval data type can also be cast on Google BigQuery as INTERVAL. It can be used to represent the following data type expressions:

• STRING

Syntax:

CAST(expression AS INTERVAL)

Example:

SELECT input, CAST(input AS INTERVAL) AS output
FROM UNNEST([
  '1-2 3 10:20:30.456',
  '1-2',
  '10:20:30',
  'P1Y2M3D',
  'PT10H20M30,456S'
]) input

10) CAST AS NUMERIC

The numeric data type can also be cast on Google BigQuery as NUMERIC. It can be used to represent the following data types expressions:

• BIGNUMERIC
• FLOAT64
• INT64
• NUMERIC
• STRING

Syntax:

CAST(expression AS NUMERIC)

11) CAST AS STRING

The string data type can also be cast on Google BigQuery as STRING. One of the reasons to use bigquery cast integer to string is to concatenate integer values with string values. It can be used to represent the following data types expressions:

• BIGNUMERIC
• BOOL
• BYTES
• DATE
• DATETIME
• FLOAT64
• INT64
• NUMERIC
• STRING
• TIME
• TIMESTAMP

Syntax:

CAST(expression AS STRING [format_clause [AT TIME ZONE timezone_expr]])

Examples:

SELECT CAST(CURRENT_DATE() AS STRING) AS current_date

SELECT CAST(CURRENT_DATE() AS STRING FORMAT 'DAY') AS current_day

SELECT CAST(
     TIMESTAMP '2008-12-25 00:00:00+00:00'
     AS STRING FORMAT 'YYYY-MM-DD HH24:MI: SS TZH: TZM') AS date_time_to_string

SELECT CAST(
          TIMESTAMP '2008-12-25 00:00:00+00:00'
      AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM'
     AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string

12) CAST AS STRUCT

The struct data type can also be cast on Google BigQuery as STRUCT. It can be used to represent the following data type expression:

• STRUCT

Syntax:

CAST(expression AS STRUCT)

13) CAST AS TIME

The time data type can also be cast on Google BigQuery as TIME. It can be used to represent the following data types expressions:

• DATETIME
• STRING
• TIME
• TIMESTAMP

When you need to cast an expression of a particular data type to another, the format clause can be used to offer directives for carrying out the cast, especially if the expression is a string. 

Syntax:

CAST(expression AS TIME [format_clause])

14) CAST AS TIMESTAMP

The timestamp data type can also be cast on Google BigQuery as TIMESTAMP. It can be used to represent the following data types expressions:

• DATETIME
• STRING
• TIME
• TIMESTAMP

When you need to cast an expression of a specific data type to another, the format clause can offer directives for carrying out the cast, especially if the expression is a string. 

Syntax:

CAST(expression AS TIMESTAMP [format_clause [AT TIME ZONE timezone_expr]])

The timestamp format clause comes with an added clause known as the AT TIME ZONE timezone_expr. The clause can be used to state a particular time zone during formatting. As long as you don’t include this optional clause, the system uses your current time zone. 

Examples:

Take a look at some examples of a string-formatted timestamp as a timestamp:

SELECT CAST("2021-09-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp

SELECT CAST("06/09/2021 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE 'America/Los_Angeles') AS as_timestamp

SELECT CAST("06/09/2021 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE '00') AS as_timestamp

SELECT CAST('06/09/2021 17:00:53.110 +00' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS TZH') AS as_timestamp

15) PARSE_BIGNUMERIC

This data type expression is used to convert a string into a BiGNUMERIC value. One rule is that the string is numeric literal and should not pass the maximum precision or the BIGNUMERIC type range unless you get an error. If the digits number exceeds 38, the next BIGNUMERIC value starts from zero to 38 digits after the decimal. 

Syntax:

PARSE_BIGNUMERIC(string_expression)

Examples:

SELECT PARSE_BIGNUMERIC("123.45") AS parsed

SELECT PARSE_BIGNUMERIC("123.456E37") AS parsed

SELECT PARSE_BIGNUMERIC("1.123456789012345678901234567890123456789") as parsed

This function is the same as the BIGNUMERIC cast function, but the PARSE_BIGNUMERIC function allows the following in the string and only agrees to string inputs. 

SELECT PARSE_BIGNUMERIC("12.34e-1-") as parsed;

SELECT PARSE_BIGNUMERIC("  -  12.34 ") as parsed;

SELECT PARSE_BIGNUMERIC("  1,2,,3,.45 + ") as parsed;

SELECT PARSE_BIGNUMERIC(".1234  ") as parsed;

16) PARSE_NUMERIC

Change a string to NUMERIC. The numeric exacting embedded in the string should not surpass the highest precision or the NUMERIC value range or display an error. If the digits after the decimal point reach nine, the subsequent NUMERIC value adjusts half away from zero to nine numbers after the decimal point.

Syntax:

PARSE_NUMERIC(string_expression)

Examples:

SELECT PARSE_NUMERIC("123.45") AS parsed

SELECT PARSE_NUMERIC("12.34E27") as parsed

SELECT PARSE_NUMERIC("1.0123456789") as parsed

17) SAFE_CAST

A query may fail while using CAST, and that occurs when Google BigQuery can’t perform the cast, the following error is generated by the query:

Syntax:

SAFE_CAST(expression AS typename [format_clause])

Examples:

SELECT CAST("apple" AS INT64) AS not_a_number;

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

SELECT
  safe_CAST(date1 AS DATE) AS date1,
  safe_CAST(date2 AS DATE) AS date2,
  FORMAT_DATE('%b %d,%Y', safe_CAST(date2 AS DATE)) AS formatted_date2
FROM
  (
    SELECT
      '20/9/21' AS date1,
      '2021-09-20' AS date2
  ) AS table_1

We used SAFE_CAST here so that if the CAST to date failed, the whole query would not fail. In the example above, date1 returns a NULL value since it’s not in the right format. Similar rules apply for converting STRINGs to DATETIME, TIMESTAMP, and TIME:

When casting STRING -> DATETIME, the string must be in the format YYYY:MM: DD HH:MM: SS

SELECT
  CAST('2020-12-25 03:22:01' AS DATETIME) AS str_to_datetime

When casting STRING -> TIMESTAMP, the string must be in a format YYYY:MM: DD HH:MM: SS [timestamp] (where the timestamp defaults to UTC if not specified).

SELECT
  CAST('2020-12-25 03:22:01-5:00' AS TIMESTAMP) AS str_to_timestamp

When casting STRING -> TIME the string must be in the format HH:MM:SS

select cast('15:03:11' as TIME) as str_to_time

Format Clause for CAST

This can be utilized in some CAST functions. It is used to offer guidelines on how to carry out a cast. For instance, you could train a cast to change a succession of bytes over to a BASE64-encoded string rather than a UTF-8-encoded string. The configuration statement incorporates a format model. This model can contain elements joined together to form a format string.

Syntax:

format_clauses:
  formats format_model

format_models:
  format_string_expressions

1) Format BYTES as STRING

Syntax:

CAST(bytes_expression AS STRING FORMAT format_string_expression)

Example: 

SELECT CAST(b'x48x65x6cx6cx6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;

2) Format STRING as BYTES

Syntax:

CAST(string_expression AS BYTES FORMAT format_string_expression)

Example:

SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes

Other conversion functions

You can learn more about these conversion functions elsewhere in the documentation:

Conversion function	From	To
ARRAY_TO_STRING	ARRAY	STRING
BOOL	JSON	BOOL
DATE	Various data types	DATE
DATE_FROM_UNIX_DATE	INT64	DATE
DATETIME	Various data types	DATETIME
FLOAT64	JSON	FLOAT64
FROM_BASE32	STRING	BYTEs
FROM_BASE64	STRING	BYTES
FROM_HEX	STRING	BYTES
INT64	JSON	INT64
PARSE_DATE	STRING	DATE
PARSE_DATETIME	STRING	DATETIME
PARSE_JSON	STRING	JSON
PARSE_TIME	STRING	TIME
PARSE_TIMESTAMP	STRING	TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING	BYTES	STRING
STRING	TIMESTAMP	STRING
STRING	JSON	STRING
TIME	Various data types	TIME
TIMESTAMP	Various data types	TIMESTAMP
TIMESTAMP_MICROS	INT64	TIMESTAMP
TIMESTAMP_MILLIS	INT64	TIMESTAMP
TIMESTAMP_SECONDS	INT64	TIMESTAMP
TO_BASE32	BYTES	STRING
TO_BASE64	BYTES	STRING
TO_HEX	BYTES	STRING
TO_JSON	All data types	JSON
TO_JSON_STRING	All data types	STRING

You can also learn about Bigquery Parameterized Queries.

Conclusion

This article gave you a brief introduction to Google BigQuery CAST Functions. It also provided in-depth knowledge about Google BigQuery CAST functions syntax along with their examples. Now, you can easily utilize the CAST Functions to convert between data types in Google BigQuery with ease.

Businesses can use automated platforms like Hevo Data to set the integration and handle the ETL process. Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

FAQs