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)returns42.SELECT CAST('-7' AS INT64)returns-7(negative numbers work).SELECT CAST(' 123 ' AS INT64)returns123(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')returns1234.SELECT PARSE_INT64('+56')returns56.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.