十六进制字符串到Amazon Redshift中的整数转换 [英] Hex string to integer conversion in Amazon Redshift

问题描述

Amazon Redshift基于ParAccel，而ParAccel基于Postgres。从我的研究看来，在Postgres中执行十六进制字符串到整数转换的首选方法是通过位字段，如本答案。

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.

如果是bigint，则为：

In the case of bigint, this would be:

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

不幸的是，这在Redshift上失败，原因是：

Unfortunately, this fails on Redshift with:

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

还有什么在Postgres 8.1ish中有什么方法可以执行此转换（接近Redshift的兼容性级别）？ Redshift不支持UDF，数组，正则表达式函数或集合生成函数也不支持UDF ...

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...

推荐答案

在某个时候为此功能： STRTOL

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

语法

STRTOL（num_string，base）

STRTOL(num_string, base)

返回类型

BIGINT。如果num_string为null，则返回NULL。

BIGINT. If num_string is null, returns NULL.

例如

SELECT strtol('deadbeef', 16);

返回： 3735928559

这篇关于十六进制字符串到Amazon Redshift中的整数转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！