BigQuery Convert String to Integer: CAST, SAFE_CAST, PARSE

How-to basics · Updated November 22, 2026

To convert a string to an integer in BigQuery, use the CAST function: CAST(string_expression AS INT64). For example, SELECT CAST('123' AS INT64) returns the integer 123. If the string might contain non-numeric characters, use SAFE_CAST instead, which returns NULL instead of throwing an error.

Why String-to-Integer Conversion Matters

BigQuery stores data in strongly typed columns, but raw data often arrives as strings—from CSV uploads, JSON payloads, or external APIs. When you need to perform arithmetic, join on numeric keys, or filter with numeric comparisons, you must convert those strings to integers. Without conversion, operations like WHERE price > 10 treat '10' as text, leading to lexicographic comparisons (e.g., '9' > '10' is true). Converting ensures correct numeric behavior.

Using CAST for Direct Conversion

The simplest method is CAST. The syntax is:

CAST(expression AS INT64)

Here are concrete examples:

  • SELECT CAST('42' AS INT64) returns 42.
  • SELECT CAST('-7' AS INT64) returns -7 (negative numbers work).
  • SELECT CAST(' 123 ' AS INT64) returns 123 (leading/trailing spaces are trimmed).

If the string is not a valid integer, CAST raises an error. For instance, CAST('12.5' AS INT64) fails because 12.5 is not an integer. Similarly, CAST('abc' AS INT64) errors out.

SAFE_CAST: Handling Invalid Strings Gracefully

When your data may contain dirty values, SAFE_CAST is the safer choice. It returns NULL for any string that cannot be converted, instead of aborting the query. Example:

SELECT SAFE_CAST('12.5' AS INT64)  -- returns NULL
SELECT SAFE_CAST('abc' AS INT64)   -- returns NULL
SELECT SAFE_CAST('123' AS INT64)   -- returns 123

This is particularly useful in ETL pipelines where you want to keep bad rows but flag them. You can combine it with COALESCE to provide a default value:

SELECT COALESCE(SAFE_CAST(user_id AS INT64), 0) AS user_id_int FROM dataset.table

PARSE_INT64: When You Need Format Control

BigQuery also offers PARSE_INT64, which is designed for strings that include formatting like commas or currency symbols. The syntax is:

PARSE_INT64(string_expression)

It accepts a limited set of patterns: an optional sign, digits, and optional commas as thousands separators. For example:

  • SELECT PARSE_INT64('1,234') returns 1234.
  • SELECT PARSE_INT64('+56') returns 56.
  • SELECT PARSE_INT64('12,34') fails because the comma is not in the right position.

Unlike CAST, PARSE_INT64 does not trim spaces—it requires the entire string to match the pattern. It also throws an error on invalid input; there is no SAFE_PARSE_INT64 function. If you need error tolerance with commas, first strip commas with REPLACE and then use SAFE_CAST.

Comparing CAST, SAFE_CAST, and PARSE_INT64

Choosing the right function depends on your data and error tolerance:

Function Handles spaces Handles commas On invalid input
CAST Yes (trims) No Error
SAFE_CAST Yes (trims) No NULL
PARSE_INT64 No Yes (proper placement) Error

For most cases, SAFE_CAST is the recommended default because it prevents query failures. Use CAST when you are certain the data is clean and you want errors to surface. Use PARSE_INT64 only when your strings contain thousands separators and you are okay with strict formatting.

Common Pitfalls and Misconceptions

A frequent mistake is assuming CAST can convert decimal strings to integers. It cannot—it will error. You must first convert to FLOAT64 or NUMERIC, then round or truncate. For example:

SELECT CAST(CAST('12.7' AS FLOAT64) AS INT64)  -- returns 13 (rounds)

But note that CAST from float to int rounds half away from zero. If you want truncation, use TRUNC:

SELECT CAST(TRUNC(CAST('12.7' AS FLOAT64)) AS INT64)  -- returns 12

Another misconception is that SAFE_CAST converts floats to integers—it does not; it only accepts integer strings. Also, be aware that BigQuery’s INT64 is a 64-bit signed integer, so strings representing numbers beyond ±9,223,372,036,854,775,807 will fail. For very large numbers, consider NUMERIC or BIGNUMERIC.

Performance and Cost Considerations

Conversion functions do not significantly impact query cost, which is based on bytes processed. However, if you frequently convert the same column, consider storing the converted value in a new column or using a view to avoid repeated conversion overhead. In BigQuery, you can create a persistent column with CAST during a load job or use a SQL view:

CREATE VIEW mydataset.vw_users AS
SELECT user_id, SAFE_CAST(user_id AS INT64) AS user_id_int
FROM mydataset.users_raw

This lets you query the converted column without re-writing the conversion logic each time. There is no extra cost for using SAFE_CAST versus CAST; both process the same amount of data.

Alternatives: Using Regular Expressions

If your strings contain extra characters like currency symbols or letters, you can extract the numeric part with REGEXP_EXTRACT before casting. For example, to extract the integer from '$1,200':

SELECT SAFE_CAST(REPLACE(REGEXP_EXTRACT('$1,200', r'[0-9,]+'), ',', '') AS INT64)  -- returns 1200

This is a two-step process: first extract digits and commas, then remove commas and cast. This approach is more flexible but slower and more error-prone. Use it only when the built-in functions cannot handle your format.

Frequently Asked Questions

Can I convert a string with a decimal point to an integer directly?

No. CAST and SAFE_CAST only accept integer strings. You must first convert to FLOAT64 or NUMERIC, then round or truncate as shown above.

What happens if I use CAST on an empty string?

CAST('' AS INT64) throws an error. SAFE_CAST('' AS INT64) returns NULL. If you want to treat empty strings as 0, use COALESCE(SAFE_CAST(col AS INT64), 0).

Does BigQuery support converting strings with leading zeros?

Yes. CAST('007' AS INT64) returns 7. Leading zeros are ignored in integer conversion.

Is there a difference between INT64 and INTEGER in BigQuery?

No, they are aliases. You can use either in CAST, e.g., CAST(x AS INTEGER) works the same as CAST(x AS INT64).

How do I convert a string to an integer in a SELECT * query without listing all columns?

You cannot automatically convert in SELECT *. You must explicitly list the column with the conversion, e.g., SELECT *, SAFE_CAST(id AS INT64) AS id_int FROM table. For a table schema change, use a DDL statement like ALTER TABLE ... ALTER COLUMN id SET DATA TYPE INT64, but only if the column already contains valid integer strings.

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.