BigQuery - Cast HEX string to NUMERIC or BIGNUMERIC?

Question

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.

Answer 1

For such big numbers, not even BIGNUMERIC will fit them, so you will have to work as string. Regular BigQUery functions will not be able to handle that numbers, so I suggest you to use a UDF:

CREATE TEMP FUNCTION from_hex_to_intstring(hex STRING)
RETURNS STRING   
LANGUAGE js AS r"""
    yourNumber = BigInt(hex,16);
    return yourNumber;
  """;

select from_hex_to_intstring('0x00000000000000000000000000000000000000000000000000000000000000006cc09155dd769741d7cd1c6a3334a1aeef62da2d0e92a39230becd6e56c2ad490000000000000000000000000000000000000000000000007ce66c50e2840000') data;
select  from_hex_to_intstring('0x00000000000000000000000000000000000000000009ed194db19b238c000000') data

Results:

-------------------------------
Row | data  
1   | 5695815805094697319662327076913960577653781492348607706655047793592681546373383993595483025021696631917691807178407718241565809060633202962632700189736960
-------------------------------
Row | data  
1   | 12000000000000000000000000
-------------------------------

Bonus 1: If the hex is not that big you can return it as NUMERIC or BIGNUMERIC with:

select cast(from_hex_to_intstring(<hex string>) as NUMERIC)

Bonus 2: If you want to trim the zeros on your hex use the following (But its not required for the function above):

select concat("0x",ltrim('0x00000000000000000000000000000000000000000009ed194db19b238c000000',"0x")) as data

-------------------------------
Row | data  
1   | 0x9ed194db19b238c000000
-------------------------------

• I recommend you to work only with string, not cast to NUMERIC.