Tag: google-bigquery

BigQuery – Cast HEX string to NUMERIC or BIGNUMERIC?

Posted on by Andeas

I’ve got a data string in a hex format. Something like

'0x00000000000000000000000000000000000000000000000000000000000000006cc09155dd769741d7cd1c6a3334a1aeef62da2d0e92a39230becd6e56c2ad490000000000000000000000000000000000000000000000007ce66c50e2840000' as data

I know that substring(data, 131) is a large number.

I can pass SAFE_CAST(CONCAT('0x', SUBSTRING(data, 131)) AS INT64) just fine on the smaller numbers.

SAFE_CAST(CONCAT('0x', SUBSTRING(data, 131)) AS NUMERIC) (or bignumeric) won’t work.

I tried something like FROM_HEX(SUBSTRING(data, 131)) to get a byte format. But couldn’t find any good options for getting BYTE to NUMERIC either.