How to covert hex to integer

Dremio University
1

Hello Dremio,

I am trying to convert the first byte from MD5 digest (2 first letters which represents first hex value) and convert it to INT value.

For instance I used following statement but it returns “Input buffer segment is of invalid length” error:

select convert_from(from_hex(substring(md5('example string'),0,2)),'INT')

This query
select 'hello' as str_value, md5('hello') as md5, substring(md5('hello'),0,2) as firstHex, from_hex(substring(md5('hello'),0,2)) as bin_hex
returns:

|str_value|md5|firstHex|bin_hex|
|hello|5d41402abc4b2a76b9719d911017c592|5d|XQ==|

in the firstHex column ‘0x5d’ value which in decimal system is 93 and this value I would like get.

I looking for something equivalent for the Postgresql counterpart:
select GET_BYTE(DECODE(SUBSTRING(MD5(CAST("hello" AS TEXT)) for 2), 'hex'), 0), 0)

How to achieve this ?
Thank you in advance
Marcin

2

If I’m reading this right, you want to convert hexadecimal to decimal?

Try something like:

 select conv(substring(md5('hello'),0,2),16,10) as "base10"

image
image1192×476 32 KB

3

That’s exactly what I needed. Thank you very much @lenoyjacob