Converting large values to/from hex

[aschneider]

The following behavior is frustrating to me:

Code: Select all

select 0x4318F7A9055EA43237BE62E130338B33C7D6AD43;
                     ?column?                     
--------------------------------------------------
 383059173779444683676001354605068116670933282115
(1 row)

select HEX_TO_INTEGER('4318F7A9055EA43237BE62E130338B33C7D6AD43');
ERROR 5408:  Value "0x4318F7A9055EA43237BE62E130338B33C7D6AD43" is out of range for type numeric(37,15)

select TO_HEX(383059173779444683676001354605068116670933282115);
ERROR 3457:  Function TO_HEX(numeric) does not exist, or permission is denied for TO_HEX(numeric)

In other words, Vertica can convert hex literals into numerics, but the SQL functions to do this only work up to a certain size. Am I doing something wrong, or is there a better way to convert large integers to/from hex?

[JimKnicely]

Hi,

The largest integer data type in Vertica is INT8 which stores up to 64-bit values. So the largest value would be 9,223,372,036,854,775,808.

Your HEX value of "4318F7A9055EA43237BE62E130338B33C7D6AD43" is 383,059,173,779,444,683,676,001,354,605,068,116,670,933,282,115 in decimal! That's a bit bigger than what Vertica can handle.

Note: I used the site http://www.mathsisfun.com/binary-decima ... erter.html to convert your hex to decimal.

You can convert the large HEX to a bit string...

Code: Select all

dbadmin=> SELECT TO_BITSTRING(HEX_TO_BINARY('4318F7A9055EA43237BE62E130338B33C7D6AD43'));
                                                                           TO_BITSTRING
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 0100001100011000111101111010100100000101010111101010010000110010001101111011111001100010111000010011000000110011100010110011001111000111110101101010110101000011
(1 row)

But that's probably not going to help you...

[aschneider]

Ah, that makes sense - I'm starting to understand the errors a bit better. So what I need is a function that converts hex to the unbounded NUMERIC type rather than INTEGER?