Presto – hex string to int

I’m trying to convert hex string (starts with ‘0x’) to it’s integer value using presto. For example 0x100 to 256. My hex string is called msg_id. I tried to use this-

from_hex(substr(msg_id,3))

But I run into a problem, because from_hex expect even number of hex digits (0100 instead of 100). I decided to try and solve this using an if statement, so I tried this:

if(length(msg_id)%2=0, from_hex(substr(msg_id,3))) 

(will take care of the odd number of digits case later)

But- the result of from_hex is a varbinary type, with a varied number of bytes. I want to convert it to an Integer, or any other numeric type, but I can’t find a way to do it.

Any ideas? I would appreciate it…

Answer

You can use from_base(string, radix) to parse number written with hex digits into a bigint. You just need to strip leading '0x' first:

select from_base(substr('0x100', 3), 16);
 _col0
-------
   256

or with regexp_replace():

presto:tiny> select from_base(regexp_replace('0x100', '^0x'), 16);
 _col0
-------
   256