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.

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 3

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

All BigQuery lessons