postgresql 将文本表示中的十六进制转换为十进制数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8316164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert hex in text representation to decimal number
提问by Hartwig
I am trying to convert hex to decimal using PostgreSQL 9.1
我正在尝试使用 PostgreSQL 9.1 将十六进制转换为十进制
with this query:
使用此查询：
SELECT to_number('DEADBEEF', 'FMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
I get the following error:
我收到以下错误：
ERROR: invalid input syntax for type numeric: " "
What am I doing wrong?
我究竟做错了什么？
采纳答案by mu is too short
You have two immediate problems:
你有两个直接的问题：
to_numberdoesn't understand hexadecimal.Xdoesn't have any meaning in ato_numberformat string and anything without a meaning apparently means "skip a character".
to_number不懂十六进制。X在to_number格式字符串中没有任何意义，任何没有意义的东西显然都意味着“跳过一个字符”。
I don't have an authoritative justification for (2), just empirical evidence:
我对（2）没有权威的理由，只有经验证据：
=> SELECT to_number('123', 'X999');
to_number
-----------
23
(1 row)
=> SELECT to_number('123', 'XX999');
to_number
-----------
3
The documentation mentions how double quoted patterns are supposed to behave:
文档提到了双引号模式应该如何表现：
In
to_date,to_number, andto_timestamp, double-quoted strings skip the number of input characters contained in the string, e.g."XX"skips two input characters.
在
to_date,to_number, 和 中to_timestamp，双引号字符串跳过字符串中包含的输入字符数，例如"XX"跳过两个输入字符。
but the behavior of non-quoted characters that are not formatting characters appears to be unspecified.
但非格式化字符的非引号字符的行为似乎未指定。
In any case, to_numberisn't the right tool for converting hex to numbers, you want to say something like this:
在任何情况下，to_number都不是将十六进制转换为数字的正确工具，您想这样说：
select x'deadbeef'::int;
so perhaps this functionwill work better for you:
所以也许这个功能更适合你：
CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$
DECLARE
result int;
BEGIN
EXECUTE 'SELECT x' || quote_literal(hexval) || '::int' INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Then:
然后：
=> select hex_to_int('DEADBEEF');
hex_to_int
------------
-559038737 **
(1 row)
** To avoid negative numbers like this from integer overflow error, use bigint instead of int to accommodate larger hex numbers (like IP addresses).
** 为避免整数溢出错误中的负数，请使用 bigint 而不是 int 来容纳更大的十六进制数（如 IP 地址）。
回答by Erwin Brandstetter
There are ways without dynamic SQL.
有没有动态 SQL 的方法。
Max. 8 hex digits
最大限度。8 个十六进制数字
There is no cast from hex numbers in textrepresentation to a numeric type, but we can use bit(n)as waypoint. 4 bitsin a bit string encode 1 hex digit. There is an undocumented cast from bit strings up to bit(32)(max. 8 hex digits) to integer(standard 4-byte integer) - the internal representation is binary compatible.
没有从text表示形式的十六进制数字转换为数字类型，但我们可以bit(n)用作航点。位串中的4 位编码 1 个十六进制数字。有一个从位串到bit(32)（最多 8 个十六进制数字）到integer（标准 4 字节整数）的未公开转换 - 内部表示是二进制兼容的。
SELECT ('x' || lpad(hex, 8, '0'))::bit(32)::int AS int_val
FROM (
VALUES ('1'::text)
,('f')
,('100')
,('7fffffff')
,('80000000')
,('deadbeef')
,('ffffffff')
) AS t(hex);Result:
结果：
int_val
------------
1
15
256
2147483647
-2147483648
-559038737
-1
4 bytes are enough to encode allhex numbers up to 8 digits but integerin Postgres is a signed type, so hex numbers above '7fffffff'overflow into a negative intnumber. This is still a unique representation, but the meaningis different. If that matters switch to bigint, see below.
4 个字节足以编码 最多 8 位的所有十六进制数，但integer在 Postgres 中是有符号类型，因此上面的'7fffffff'十六进制数溢出为负整数。这仍然是一种独特的表示，但含义不同。如果这很重要bigint，请切换到，请参见下文。
For hex numbers of unknown varying lengthwe need to pad leadingzeros 0as demonstrated to cast to bit(32). For numbers of known length we can just adapt the length specifier. Example with 7 hex digits and intor 8 digits and bigint:
对于未知长度的十六进制数，我们需要填充前导零，0如所演示的那样转换为bit(32). 对于已知长度的数字，我们可以调整长度说明符。7 个十六进制数字和int或 8 个数字和的示例bigint：
SELECT ('x'|| 'deafbee')::bit(28)::int
, ('x'|| 'deadbeef')::bit(32)::bigint;
int4 | int8
-----------+------------
233503726 | 3735928559
Max. 16 hex digits
最大限度。16 位十六进制数字
Use bigint(int8, 8-byte integer) for up to 16 hex digits - overflowing to negative numbers in the upper half:
使用bigint( int8, 8 字节整数) 表示最多 16 个十六进制数字 - 上半部分溢出到负数：
SELECT ('x' || lpad(hex, 16, '0'))::bit(64)::bigint AS int8_val
FROM (
VALUES ('ff'::text)
, ('7fffffff')
, ('80000000')
, ('deadbeef')
, ('7fffffffffffffff')
, ('8000000000000000')
, ('ffffffffffffffff')
, ('ffffffffffffffff123') -- too long
) t(hex);Result:
结果：
int8_val
---------------------
255
2147483647
2147483648
3735928559
9223372036854775807
-9223372036854775808
-1
-1
For more than 16 hex digits the least significant characters (excess to the right) get truncated.
对于超过 16 个十六进制数字，最不重要的字符（超出右侧）被截断。
This cast relies on undocumented behavior, I quote Tom Lane here:
This is relying on some undocumented behavior of the bit-type input converter, but I see no reason to expect that would break. A possibly bigger issue is that it requires PG >= 8.3 since there wasn't a text to bit cast before that.
这依赖于位类型输入转换器的一些未记录的行为，但我认为没有理由期望它会中断。一个可能更大的问题是它需要 PG >= 8.3，因为在此之前没有要位转换的文本。
UUID for max. 32 hex digits
最大 UUID 32 位十六进制数字
The Postgres uuiddata type is not a numeric type, so this deviates from the question asked. But it's the most efficient type in standard Postgres to store up to 32 hex digits, only occupying 16 bytes of storage. There is a direct cast, but exactly32 hex digits are required.
Postgresuuid数据类型不是数字类型，因此这偏离了所提出的问题。但它是标准 Postgres 中最有效的类型，最多可存储 32 个十六进制数字，仅占用 16 个字节的存储空间。有一个direct cast，但正好需要32 个十六进制数字。
SELECT lpad(hex, 32, '0')::uuid AS uuid_val
FROM (
VALUES ('ff'::text)
, ('deadbeef')
, ('ffffffffffffffff')
, ('ffffffffffffffffffffffffffffffff')
, ('ffffffffffffffffffffffffffffffff123') -- too long
) t(hex);Result:
结果：
uuid_val
--------------------------------------
00000000-0000-0000-0000-0000000000ff
00000000-0000-0000-0000-0000deadbeef
00000000-0000-0000-ffff-ffffffffffff
ffffffff-ffff-ffff-ffff-ffffffffffff
ffffffff-ffff-ffff-ffff-ffffffffffff
As you can see, standard output is a string of hex digits with typical separators for UUID.
如您所见，标准输出是一串十六进制数字，带有典型的 UUID 分隔符。
md5 hash
md5 哈希
This is particularly useful to store md5 hashes:
这对于存储md5 哈希特别有用：
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash
Result:
结果：
md5_hash
--------------------------------------
02e10e94-e895-616e-8e23-bb7f8025da42
回答by Pierre D
If anybody else is stuck with PG8.2, here is another way to do it.
如果其他人坚持使用 PG8.2，这是另一种方法。
bigint version:
bigint 版本：
create or replace function hex_to_bigint(hexval text) returns bigint as $$
select
(get_byte(x,0)::int8<<(7*8)) |
(get_byte(x,1)::int8<<(6*8)) |
(get_byte(x,2)::int8<<(5*8)) |
(get_byte(x,3)::int8<<(4*8)) |
(get_byte(x,4)::int8<<(3*8)) |
(get_byte(x,5)::int8<<(2*8)) |
(get_byte(x,6)::int8<<(1*8)) |
(get_byte(x,7)::int8)
from (
select decode(lpad(, 16, '0'), 'hex') as x
) as a;
$$
language sql strict immutable;
int version:
国际版：
create or replace function hex_to_int(hexval text) returns int as $$
select
(get_byte(x,0)::int<<(3*8)) |
(get_byte(x,1)::int<<(2*8)) |
(get_byte(x,2)::int<<(1*8)) |
(get_byte(x,3)::int)
from (
select decode(lpad(, 8, '0'), 'hex') as x
) as a;
$$
language sql strict immutable;
回答by Evan Carroll
pg-bignum
pg-bignum
Internally, pg-bignumuses the SSL library for big numbers. This method has none of the drawbacks mentioned in the other answers with numeric. Nor is it slowed down by plpgsql. It's fast and it works with a number of any size. Test case taken from Erwin's answer for comparison,
在内部，pg-bignum对大数字使用 SSL 库。这种方法没有其他数字答案中提到的缺点。plpgsql 也不会减慢速度。它很快，并且可以与任何大小的数量一起使用。测试用例取自 Erwin 的答案进行比较，
CREATE EXTENSION bignum;
SELECT hex, bn_in_hex(hex::cstring)
FROM (
VALUES ('ff'::text)
, ('7fffffff')
, ('80000000')
, ('deadbeef')
, ('7fffffffffffffff')
, ('8000000000000000')
, ('ffffffffffffffff')
, ('ffffffffffffffff123')
) t(hex);
hex | bn_in_hex
---------------------+-------------------------
ff | 255
7fffffff | 2147483647
80000000 | 2147483648
deadbeef | 3735928559
7fffffffffffffff | 9223372036854775807
8000000000000000 | 9223372036854775808
ffffffffffffffff | 18446744073709551615
ffffffffffffffff123 | 75557863725914323415331
(8 rows)
You can get the type to numeric using bn_in_hex('deadbeef')::text::numeric.
您可以使用bn_in_hex('deadbeef')::text::numeric.
回答by David Wolever
Here is a version which uses numeric, so it can handle arbitrarily large hex strings:
这是一个使用 的版本numeric，因此它可以处理任意大的十六进制字符串：
create function hex_to_decimal(hex_string text)
returns text
language plpgsql immutable as $pgsql$
declare
bits bit varying;
result numeric := 0;
exponent numeric := 0;
chunk_size integer := 31;
start integer;
begin
execute 'SELECT x' || quote_literal(hex_string) INTO bits;
while length(bits) > 0 loop
start := greatest(1, length(bits) - chunk_size);
result := result + (substring(bits from start for chunk_size)::bigint)::numeric * pow(2::numeric, exponent);
exponent := exponent + chunk_size;
bits := substring(bits from 1 for greatest(0, length(bits) - chunk_size));
end loop;
return trunc(result, 0);
end
$pgsql$;
For example:
例如：
=# select hex_to_decimal('ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
32592575621351777380295131014550050576823494298654980010178247189670100796213387298934358015
