Hex string to integer conversion in Amazon Redshift

Question

Amazon Redshift is based on ParAccel which is based on Postgres. From my research it seems that the preferred way to perform hexadecimal string to integer conversion in Postgres is via a bit field, as outlined in this answer.

In the case of bigint, this would be:

select ('x'||lpad('123456789abcdef',16,'0'))::bit(64)::bigint

Unfortunately, this fails on Redshift with:

ERROR: cannot cast type text to bit [SQL State=42846] 

What other ways are there to perform this conversion in Postgres 8.1ish (that's close to the Redshift level of compatibility)? UDFs are not supported in Redshift and neither are array, regex functions or set generating functions...

Answer 1

It looks like they added a function for this at some point: STRTOL

Syntax

STRTOL(num_string, base)

Return type

BIGINT. If num_string is null, returns NULL.

For example

SELECT strtol('deadbeef', 16);

Returns: 3735928559