Strings, REGEXP_CONTAINS and SAFE_CAST
Data often arrives as text with commas, spaces and placeholders in it. String functions clean it up, and SAFE_CAST turns it into numbers without stopping the whole query.
How it works
CONCAT, LOWER and REPLACE build or clean text one value at a time. CAST converts a value to another type and fails the whole query on a value it cannot convert. SAFE_CAST does the same conversion and returns NULL for that value instead.
CONCAT(make, ' ', registration)joins text values.LOWER(text)turns the letters into lower case.REPLACE(text, ',', '')removes every comma from the text.SAFE_CAST(text AS INT64)returnsNULLwhen the text is not a whole number, for example when it has a comma or reads N/A.CAST(text AS INT64)raises an error for the same text.REGEXP_CONTAINS(text, r'^[0-9]+$')isTRUEwhen the text matches the pattern. This pattern means only digits from start to end.
Common mistake
SAFE_CAST turns a failed conversion into NULL and does nothing to repair the value. The text '82,410' becomes NULL, which looks like a missing reading. Clean the text with REPLACE first, then cast.
Worked example
SELECT registration, SAFE_CAST(odometer_value AS INT64) AS odometer
FROM `learn.mot.raw_mot_intake`
ORDER BY registration
LIMIT 3raw_mot_intake is a small table of eight hand-written intake rows. Its odometer_value is text.
A clean value such as 54000 becomes a number. A value with a comma becomes NULL.