Convert the MD5 output into 32 bit integer in Redshift

Question

I have tried the following in Redshift

SELECT STRTOL(MD5('345793260804895811'), 10);

but I got the following DBCException:

SQL Error [22023]: ERROR: The input cf82576a6dbf9ff63cf9828f990f0673 is not valid to be converted to base 10

org.postgresql.util.PSQLException: PSQLException: ERROR: The input cf82576a6dbf9ff63cf9828f990f0673 is not valid to be converted to base 10

How may I get this done in Redshift?

Answer 1

You have 2 problems:

• First, you need to specify the conversion as being base 16
• Second, an MD5 string will massively overflow a 64 bit BIGINT value

This works nicely

SELECT STRTOL(LEFT(MD5('345793260804895811'),15), 16);

Shortens the MD5 hex value to 15 leftmost characters and convert to a BIGINT using base 16.

Answer 2

I came up with this to store MD5 in two BIGINT fields instead of CHAR(32) - 2x space saving!

select 
    convert(bigint,
        strtol(substring(hash,1,8),16) * 4294967296.0 +
        strtol(substring(hash,9,8),16) - 9223372036854775807
    ) as hash_part1
    ,convert(bigint,
        strtol(substring(hash,17,8),16) * 4294967296.0 +
        strtol(substring(hash,25,8),16) - 9223372036854775807
    ) as hash_part2

Hope it helps someone.

Answer 3

The result of MD5 is 128 bit long(ref), you can not fit it into a 32 bit integer.

Answer 4

You can try with converting on base 16 instead of 10:

SELECT STRTOL(MD5('cf82576a6dbf9ff63cf9828f990f0673'), 16);