Google BigQuery CAST Functions 101: Syntax and Usage Simplified

| |

Google BigQuery Cast Functions

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

FunctionsDescription
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.

To know more about Google BigQuery CAST Functions, visit this link.

Simplify Data Analysis Using Hevo’s No-code Data Pipeline

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Check out what makes Hevo amazing:

  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
Sign up here for a 14-Day Free Trial!

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:

1) Google BigQuery 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.

Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Learn the best practices and considerations for setting up high-performance ETL to BigQuery

2) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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) Google BigQuery 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 functionFromTo
ARRAY_TO_STRINGARRAYSTRING
BOOLJSONBOOL
DATEVarious data typesDATE
DATE_FROM_UNIX_DATEINT64DATE
DATETIMEVarious data typesDATETIME
FLOAT64JSONFLOAT64
FROM_BASE32STRINGBYTEs
FROM_BASE64STRINGBYTES
FROM_HEXSTRINGBYTES
INT64JSONINT64
PARSE_DATESTRINGDATE
PARSE_DATETIMESTRINGDATETIME
PARSE_JSONSTRINGJSON
PARSE_TIMESTRINGTIME
PARSE_TIMESTAMPSTRINGTIMESTAMP
SAFE_CONVERT_BYTES_TO_STRINGBYTESSTRING
STRINGTIMESTAMPSTRING
STRINGJSONSTRING
TIMEVarious data typesTIME
TIMESTAMPVarious data typesTIMESTAMP
TIMESTAMP_MICROSINT64TIMESTAMP
TIMESTAMP_MILLISINT64TIMESTAMP
TIMESTAMP_SECONDSINT64TIMESTAMP
TO_BASE32BYTESSTRING
TO_BASE64BYTESSTRING
TO_HEXBYTESSTRING
TO_JSONAll data typesJSON
TO_JSON_STRINGAll data typesSTRING
Image source

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.

Visit our Website to Explore Hevo

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.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable Hevo Pricing that will help you choose the right plan for your business needs.

Share your experience of learning about Google BigQuery CAST Functions in the comments section below!

Samuel Salimon
Freelance Technical Content Writer, Hevo Data

Samuel specializes in freelance writing within the data industry, adeptly crafting informative and engaging content centered on data science by merging his problem-solving skills.