CAST with bigquery - Invalid NUMERIC value:

Question

I'm trying to cast a value to covert from string to integer or numeric

The string I'm trying to cast is like this: production-ross-3

but when I run SELECT CAST('production-ross-3' as numeric) it returns Invalid NUMERIC value: production-ross-3

why? it's supposed to cast from string to numeric

I'm trying to cast a value to covert from string to integer or numeric

The string I'm trying to cast is like this: production-ross-3

but when I run SELECT CAST('production-ross-3' as numeric) it returns Invalid NUMERIC value: production-ross-3

Answer 1

When using CAST, a query can fail if GoogleSQL is unable to perform the cast. If you want to protect your queries from these types of errors, you can use SAFE_CAST.

Casts between supported types that do not successfully map from the original value to the target domain produce runtime errors. ...

https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions

You have incorrectly assumed that cast will (in addition to its stated task of converting types) locate any digit in the given string. However it just does not do that and as per the warnings given it produced a runtime error.

To achieve the wanted extraction of digits from the string you can use:

-- to get the first group of digits from the left
SELECT REGEXP_EXTRACT('production-ross-3', r'\d+') AS first_digits;

-- OR, to get all digits from the string
SELECT REGEXP_EXTRACT_ALL('production-ross-3', r'\d+') AS all_digits;

So, to achieve the overall wanted outcome you can combine these e.g:

SELECT CAST(REGEXP_EXTRACT('production-ross-3', r'\d+') AS INT64) AS extracted_number;

REGEXP_EXTRACT takes two arguments: the string to extract from, and a regular expression that defines the pattern to match. Here r'\d+' is a regular expression that matches one or more digits in the string. Ref:

nb casting strings to any form of number or date is fraught with difficulty and runtime error is not uncommon. You can use SAFE_CAST to avoid those runtime errors but this simply returns NULL if it cannot make the type conversion.