-1

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

Juanca
  • 1
  • 1
  • Are you unsure about what casting/converting between data types is? A string such as "production-ross-3" is never going to be identified as any kind of valid number, so I can't really work out the result you're actually expecting that CAST to produce? – Craig Aug 31 '23 at 04:03
  • No, casting is mot magic. "3" can be cast to a number. "I expect three from magic" cannot be cast to a number. Review the literature. – Paul Maxwell Aug 31 '23 at 04:27
  • Please provide enough code so others can better understand or reproduce the problem. – Community Aug 31 '23 at 08:54
  • The documentation show that cast helps to transform from string to number, integer, or other types. I saw some videos where you can cast a string into a number, for example carlos25 and it works, maybe in bigquery is different or there are alternatives, that's what i'm asking..... – Juanca Aug 31 '23 at 15:31

1 Answers1

0

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.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51