将文本表示中的十六进制转换为十进制数

Question

将文本表示中的十六进制转换为十进制数

49

我正在尝试使用PostgreSQL 9.1将十六进制转换为十进制。

使用以下查询：

SELECT to_number('DEADBEEF', 'FMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

我得到了以下错误：

ERROR:  invalid input syntax for type numeric: " "

我做错了什么？

- Hartwig

你能否使用这个现代的答案重新评估一下吗？https://dev59.com/cmsy5IYBdhLWcg3wyxCi#47724472 - Evan Carroll

10个回答

29

你有两个紧急问题：

to_number 不理解十六进制。
X 在 to_number 格式字符串中没有任何意义，而且没有意义的字符显然意味着“跳过一个字符”。

对于问题（2），我没有权威的理由，只有经验证据：

=> SELECT to_number('123', 'X999');
 to_number 
-----------
        23
(1 row)

=> SELECT to_number('123', 'XX999');
 to_number 
-----------
         3

文档中提到双引号模式的行为应该如何处理:

在`to_date`、`to_number`和`to_timestamp`中，双引号字符串会跳过包含在字符串中的输入字符数，例如`"XX"`将跳过两个输入字符。

但是未引用的不是格式化字符的字符的行为似乎未指定。

无论如何，`to_number`并不是将十六进制转换为数字的正确工具，您需要说类似于这样的话:

select x'deadbeef'::int;

也许这个函数对你更好：

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;

接下来：

=> select hex_to_int('DEADBEEF');
 hex_to_int 
------------
 -559038737 **
(1 row)

为避免像IP地址那样较大的十六进制数字（如负数）因整数溢出错误而发生，应使用bigint代替int以容纳更大的数字。

- mu is too short

4

哈哈，真奇怪，“to_number”支持最傻的东西，比如罗马数字、序数后缀等等——上次有人需要这些是什么时候呢？但是没有十六进制吗？ - intgr

8

这里有一个版本使用了 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$;

例如：

=# select hex_to_decimal('ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
32592575621351777380295131014550050576823494298654980010178247189670100796213387298934358015

- David Wolever

无法工作于'5f68e8131ecf80000'。 - Et9

函数中存在一些错误，导致某些输入返回错误的输出结果。例如：SELECT hex_to_decimal('ffffffff00000001000000000000000000000000ffffffffffffffffffffffff'); -- 返回：115792089223836222093134215979047740691678064040465439117495607658390113746943 -- 期望：115792089210356248762697446949407573530086143415290314195533631308867097853951 - Joel Jacobson

我修正了这个函数，请看下面的帖子。现在它正常工作了。 - AVANG

我修正了这个函数，请看下面的帖子。现在它运行正常。 - undefined

8

`pg-bignum`

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)

您可以使用bn_in_hex('deadbeef')::text::numeric将类型转换为数字。

- Evan Carroll

1

有趣。可惜大多数托管的数据库只允许有限的扩展名列表。 - Erwin Brandstetter

不幸的是，这个解决方案存在一个问题，就是它不在官方软件包中（如https://wiki.postgresql.org/wiki/Apt所述），我认为这在可靠性、跨平台安装等方面有一定的风险。而且它也没有很好的文档支持。我认为bignum应该成为核心功能！ - John Frazer

4

如果您的PG8.2卡住了，这里有另一种方法解决。

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($1, 16, '0'), 'hex') as x
) as a;
$$
language sql strict immutable;

整型版本号：

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($1, 8, '0'), 'hex') as x
) as a;
$$
language sql strict immutable;

- Pierre D

1

这里是另一种实现：

CREATE OR REPLACE FUNCTION hex_to_decimal3(hex_string text)
 RETURNS numeric
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare
    hex_string_lower text := lower(hex_string);
    i int;
    digit int;
    s numeric := 0;
begin
    for i in 1 .. length(hex_string) loop
        digit := position(substr(hex_string_lower, i, 1) in '0123456789abcdef') - 1;
        if digit < 0 then
            raise '"%" is not a valid hexadecimal digit', substr(hex_string_lower, i, 1) using errcode = '22P02'; 
        end if;
        s := s * 16 + digit;
    end loop;
   
    return s;
end
$function$;

这是一个简单的函数，逐位处理数字，在使用position()函数计算输入字符串中每个字符的数值。相对于hex_to_decimal2()的优点在于它似乎更快（对于md5()生成的十六进制字符串快了4倍左右）。

- Branko Radovanovic

0

CREATE OR REPLACE FUNCTION numeric_from_bytes(bytea)
RETURNS numeric
LANGUAGE plpgsql
AS $$
declare
  bits bit varying;
  result numeric := 0;
  exponent numeric := 0;
  bit_pos integer;
begin
  execute 'SELECT x' || quote_literal(substr($1::text,3)) into bits;
  bit_pos := length(bits) + 1;
  exponent := 0;
  while bit_pos >= 56 loop
    bit_pos := bit_pos - 56;
    result := result + substring(bits from bit_pos for 56)::bigint::numeric * pow(2::numeric, exponent);
    exponent := exponent + 56;
  end loop;
  while bit_pos >= 8 loop
    bit_pos := bit_pos - 8;
    result := result + substring(bits from bit_pos for 8)::bigint::numeric * pow(2::numeric, exponent);
    exponent := exponent + 8;
  end loop;
  return trunc(result);
end;
$$;

在未来的PostgreSQL版本中，如果Dean Rasheed的补丁0001-Add-non-decimal-integer-support-to-type-numeric.patch被提交，这将变得更加简单：

CREATE OR REPLACE FUNCTION numeric_from_bytes(bytea)
RETURNS numeric
LANGUAGE sql
AS $$
SELECT ('0'||right($1::text,-1))::numeric
$$;

- Joel Jacobson

0

这里有另一个版本，它使用数字，因此可以处理任意大的十六进制字符串：

create OR REPLACE function hex_to_decimal2(hex_string text)
returns text
language plpgsql immutable as $pgsql$
declare
    bits bit varying;
    result numeric := 0;
begin
    execute 'SELECT x' || quote_literal(hex_string) INTO bits;
    while length(bits) > 0 loop
        result := result + (substring(bits from 1 for 1)::bigint)::numeric * pow(2::numeric, length(bits) - 1);
    bits := substring(bits from 2 for length(bits) - 1);
    end loop;
    return trunc(result, 0);
end
$pgsql$;

例如：

=# select hex_to_decimal('ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
32592575621351777380295131014550050576823494298654980010178247189670100796213387298934358015

例如：

=# select hex_to_decimal('5f68e8131ecf80000');
110000000000000000000

- Et9

0

这是一种正确的将十六进制转换为字符串的方法……然后您可以检查它是否为数字类型。

SELECT convert_from('\x7468697320697320612076657279206C6F6E672068657820737472696E67','utf8')

返回

this is a very long hex string

- simUser

0

David Wolever的函数hex_to_Decimal有一个错误。这是修复后的函数版本：

create or replace function ${migrSchemaName4G}.hex_to_decimal(hex_string text)
    returns numeric
    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(0, length(bits) - chunk_size) + 1;
            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 result;
end
$pgsql$;

这里有许多HEX转换为数字的例子，但大部分都对HEX的长度有限制。我需要解决在不同数据库（Oracle和Postgres）中将相同记录均匀分布在不同“篮子”中的问题。在这种情况下，不同数据库中的相同记录应该落入相同的处理“篮子”中。记录的标识符是字符串。

Oracle有一个ORA_HASH函数。但是Postgres中没有这样的函数。通过使用相同的md5缓存函数来解决了这个问题，它生成一个32位十六进制长字符串。只有上述函数才能帮助解决问题，感谢David Wolever。

对于Oracle，过滤条件如下：MOD(TO_NUMBER(standard_hash(ID, 'MD5'), 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), ${processCount}) = ${processNumber} 对于Postgres - hex_to_Decimal(MD5(ID)) % ${Processcount} = ${Processnumber}

- AVANG

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

不使用动态SQL的方法

在text表示中，没有从十六进制数转换为数字类型的转换，但我们可以使用bit(n)作为中间步骤。有一些未记录的从位字符串(bit(n))到整数类型(int2, int4, int8)的转换 - 内部表示是二进制兼容的。引用Tom Lane:

这依赖于位类型输入转换器的某些未记录行为，但我认为没有理由指望它会出错。可能更大的问题是它需要PG >= 8.3，因为在此之前没有文本到位的转换。

`integer`适用于最多8个十六进制数字

最多可以将8个十六进制数字转换为bit(32)，然后强制转换为integer (标准4字节整数)：

SELECT <b>('x' || lpad(hex, 8, '0'))::bit(32)::int</b> AS int_val
FROM  (
   VALUES
      ('1'::text)
    , ('f')
    , ('100')
    , ('7fffffff')
    , ('80000000')     -- overflow into negative number
    , ('deadbeef')
    , ('ffffffff')
    , ('ffffffff123')  -- too long
   ) AS t(hex);

   int_val
------------
          1
         15
        256
 2147483647
-2147483648
 -559038737
         -1

Postgres使用有符号整数类型，因此十六进制数字大于'7fffffff'会溢出成负整数。这仍然是一个有效的、独特的表示，但是含义不同。如果这很重要，请切换到bigint；请参见下文。

对于超过8个十六进制数字的数字，最不重要的字符（右侧多余的字符）将被截断。 4位的位字符串编码为1个十六进制数字。已知长度的十六进制数字可以直接转换为相应的bit(n)。或者，将长度未知的十六进制数字用前导零（0）填充，如演示所示，并转换为bit(32)。例如，使用7个十六进制数字和int或8个数字和bigint的示例：

SELECT ('x'|| 'deafbee')::bit(28)::int
     , ('x'|| 'deadbeef')::bit(32)::bigint;

  int4     | int8
-----------+------------
 233503726 | 3735928559

最大可容纳16位十六进制数字的`bigint`

最多可以将16位十六进制数字转换为bit(64)，然后强制转换为bigint（int8，即8字节整数） - 再次溢出为负数：

SELECT <b>('x' || lpad(hex, 16, '0'))::bit(64)::bigint</b> AS int8_val
FROM  (
   VALUES
      ('ff'::text)
    , ('7fffffff')
    , ('80000000')
    , ('deadbeef')
    , ('7fffffffffffffff')
    , ('8000000000000000')     -- overflow into negative number
    , ('ffffffffffffffff')
    , ('ffffffffffffffff123')  -- too long
   ) t(hex);

       int8_val
---------------------
                 255
          2147483647
          2147483648
          3735928559
 9223372036854775807
-9223372036854775808
                  -1
                  -1

`uuid`用于最多32个十六进制数字

Postgres uuid数据类型不是数值类型。但它是标准Postgres中存储最多32个十六进制数字最有效的类型，仅占用16字节的存储空间。可以从text直接转换为uuid（无需使用bit(n)作为航路点），但需要确切地32个十六进制数字。

SELECT <b>lpad(hex, 32, '0')::uuid</b> AS uuid_val
FROM  (
   VALUES ('ff'::text)
        , ('deadbeef')
        , ('ffffffffffffffff')
        , ('ffffffffffffffffffffffffffffffff')
        , ('ffffffffffffffffffffffffffffffff123') -- too long
   ) t(hex);

              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

如您所见，标准输出是一个带有UUID典型分隔符的十六进制数字字符串。

md5哈希

这对于存储md5哈希值特别有用：

SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;

           md5_hash
--------------------------------------
 02e10e94-e895-616e-8e23-bb7f8025da42

请参见：

什么是适用于MD5字段的最佳数据类型？

将文本表示中的十六进制转换为十进制数

不使用动态SQL的方法

integer适用于最多8个十六进制数字

最大可容纳16位十六进制数字的bigint

uuid用于最多32个十六进制数字

md5哈希

pg-bignum

`integer`适用于最多8个十六进制数字

最大可容纳16位十六进制数字的`bigint`

`uuid`用于最多32个十六进制数字

`pg-bignum`