Convert to String BigQuery: CAST & SAFE_CAST Guide

How-to basics · Updated November 22, 2026

In BigQuery, you convert a value to a string using the CAST function: CAST(expression AS STRING). For example, CAST(123 AS STRING) returns '123'. If you need error‑free conversion (returning NULL instead of failing), use SAFE_CAST(expression AS STRING).

Why Convert to STRING in BigQuery?

BigQuery is a strongly typed system. Every column has a data type such as INT64, FLOAT64, DATE, TIMESTAMP, or BOOL. Sometimes you need a text representation of that value—for concatenation, for display, for exporting to a CSV file, or for joining on a key that is stored as text in another table. Casting to STRING is the standard way to make that happen.

Common scenarios include:

  • Building a composite key like CAST(user_id AS STRING) || '-' || CAST(order_id AS STRING)
  • Formatting a numeric value with leading zeros (though LPAD is usually better for that)
  • Combining a date with a descriptive label, e.g. 'Report for ' || CAST(CURRENT_DATE() AS STRING)
  • Preparing data for tools that only accept text, such as certain BI dashboards or machine learning pipelines

How CAST Works in BigQuery

The CAST function follows the standard SQL syntax: CAST(expression AS type). For strings, the type is STRING. BigQuery also supports the shorthand expression::STRING in some contexts, but the explicit CAST is more portable and recommended.

Here are concrete examples you can run in the BigQuery console:

SELECT CAST(123 AS STRING) AS int_to_string;
-- Returns '123'

SELECT CAST(3.14 AS STRING) AS float_to_string;
-- Returns '3.14'

SELECT CAST(DATE '2024-01-15' AS STRING) AS date_to_string;
-- Returns '2024-01-15'

SELECT CAST(TIMESTAMP '2024-01-15 10:30:00 UTC' AS STRING) AS timestamp_to_string;
-- Returns '2024-01-15 10:30:00+00'

Notice that the timestamp format includes a timezone offset. BigQuery uses a default format that is not always what you want; you may need to use FORMAT_TIMESTAMP or FORMAT_DATE for custom formatting.

SAFE_CAST: The Error‑Free Alternative

If the value cannot be converted, CAST raises an error and the query fails. For instance, CAST('abc' AS INT64) fails because ‘abc’ is not a valid integer. The same applies when casting to STRING only if the source type is not castable at all—but most types are castable to STRING, so errors are rare. However, when you cast FROM a string to a number, errors are common. SAFE_CAST returns NULL instead of failing:

SELECT SAFE_CAST('123' AS INT64) AS valid_int;  -- returns 123
SELECT SAFE_CAST('abc' AS INT64) AS invalid_int; -- returns NULL

For converting TO STRING, SAFE_CAST is rarely needed because almost everything can be cast to STRING. But if you are casting from a BYTES value, SAFE_CAST can help if the bytes are not valid UTF‑8—it will return NULL instead of erroring.

Choosing Between CAST, FORMAT, and CONCAT

Not every string conversion needs CAST. BigQuery provides specialized functions for formatting:

  • FORMAT_DATE('%Y-%m-%d', date_expression) – gives you full control over date string format.
  • FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp_expression) – for timestamps.
  • FORMAT('%.2f', float_value) – formats a float with two decimal places.
  • STRING(array_expression) – converts an array to a string representation, e.g. STRING([1,2,3]) returns '[1, 2, 3]'.

Use CAST when you just need the default representation. Use FORMAT when you need a specific pattern, especially for dates and numbers. For concatenation, remember that || automatically converts arguments to strings if needed, but it is safer to be explicit.

Common Pitfalls and Misconceptions

One frequent mistake is expecting CAST(1.0 AS STRING) to return '1.00'—it actually returns '1.0'. BigQuery preserves the original precision of the float literal. If you need a fixed number of decimals, use FORMAT.

Another pitfall is casting a DATE to STRING and then trying to use that string in a JOIN with a column that is actually a DATE. The string will not match the date type, so you must cast the other column as well, or better, keep both as DATE and avoid casting.

Also note that CAST(TRUE AS STRING) returns 'true' (lowercase). If you need ‘TRUE’ or ‘1’, use a CASE expression or IF.

Performance and Cost Considerations

Casting is generally inexpensive, but doing it on large tables can add CPU time and increase slot usage. If you frequently filter or join on a casted column, consider storing the string version in a separate column during ETL. This avoids repeated casting at query time and can reduce costs because BigQuery charges by the amount of data processed, not by CPU time—but casting does use slots, which are billed per second in on‑demand pricing.

In practice, casting a column of millions of rows is fine for most queries. The bigger cost is usually scanning the column itself. If you only need the string form for a few rows, filter first, then cast.

Alternatives to CAST for Special Types

For some types, casting to STRING is not the only option:

  • BYTES: Use SAFE_CONVERT_BYTES_TO_STRING to convert bytes to a string with UTF‑8 validation.
  • ARRAY: Use ARRAY_TO_STRING(array_expression, delimiter) to get a delimited string, e.g. ARRAY_TO_STRING(['a','b'], ',') returns 'a,b'.
  • STRUCT: Casting a struct to STRING gives a JSON‑like representation, but if you need a specific format, use TO_JSON_STRING(struct_expression).
  • GEOGRAPHY: Casting to STRING returns the WKT (Well‑Known Text) representation, e.g. 'POINT(1 2)'.

Frequently Asked Questions

How do I convert a number to a string with leading zeros in BigQuery?
Use LPAD(CAST(number AS STRING), total_width, '0'). For example, LPAD(CAST(7 AS STRING), 3, '0') returns '007'.

What is the difference between CAST and SAFE_CAST when converting to STRING?
For most types, they behave identically because almost any value can be cast to STRING. The difference appears when casting from BYTES: SAFE_CAST returns NULL if the bytes are not valid UTF‑8, while CAST throws an error.

Can I cast a STRUCT to a string in BigQuery?
Yes, CAST(struct_expr AS STRING) returns a JSON‑like string, e.g. {"a":1,"b":"x"}. For a more controlled format, use TO_JSON_STRING.

Does BigQuery automatically convert types when using CONCAT?
The || operator will implicitly cast numbers and dates to strings, but it is safer to cast explicitly to avoid ambiguity. For example, SELECT 'ID: ' || 123 works, but SELECT 'Date: ' || DATE '2024-01-01' also works—both return strings.

How do I convert a timestamp to a string in a specific timezone?
First convert the timestamp to the desired timezone using TIMESTAMP or DATETIME functions, then format. For example: FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP '2024-01-15 10:30:00 UTC', 'America/New_York').

Is there a performance hit when casting a large column to STRING?
Casting itself is CPU‑intensive but relatively fast. The main cost is scanning the column. If you cast in a SELECT that scans a 1 TB table, you pay for scanning 1 TB, plus a small amount of slot time for the cast. Storing a pre‑cast column can reduce repeated costs.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.