当前位置：文江博客话题详情

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

发布于 2024-12-18 12:54:52 字数 268 浏览 3 评论 0 原文

我尝试使用 PostgreSQL 9.1

通过此查询将十六进制转换为十进制：

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

我收到以下错误：

ERROR:  invalid input syntax for type numeric: " "

我做错了什么？

原文

I am trying to convert hex to decimal using 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

橘和柠 2024-12-25 12:54:53

这是将十六进制转换为字符串的流行方法...然后您可以检查它是否是数字类型

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

this is a very long hex string

Here is a poper way to convert hex to string... then you can check whether it's a numric type or not

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

returns

this is a very long hex string

回复收藏 0 原文

等风也等你 2024-12-25 12:54:53

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

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

Here is a other version which uses numeric, so it can handle arbitrarily large hex strings:

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$;

For example:

=# select hex_to_decimal('ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
32592575621351777380295131014550050576823494298654980010178247189670100796213387298934358015

For example:

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

回复收藏 0 原文

云仙小弟 2024-12-25 12:54:53

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
$;

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;
$;

In a future PostgreSQL version, when/if Dean Rasheed's patch 0001-Add-non-decimal-integer-support-to-type-numeric.patch gets committed, this can be simplified:

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

回复收藏 0 原文

娇纵 2024-12-25 12:54:52

Postgres 16 或更高版本

.. 接受以下形式的非十进制数字常量（十六进制、八进制、二进制）：

0x十六进制数字
0o八位数字
0bbindigits

与其他 "数字常量"，默认为不会从级数中溢出的最小数字数据类型整数→bigint→数字。所以：

SELECT                0x1;  --                   1 | integer
SELECT         0x7fffffff;  --          2147483647 | integer
SELECT         0x80000000;  --          2147483648 | bigint
SELECT 0x7fffffffffffffff;  -- 9223372036854775807 | bigint
SELECT 0x8000000000000000;  -- 9223372036854775808 | numeric

这隐式适用于从 text 到 int/bigint/numeric 的转换，所以现在您可以：

SELECT ('0x'||'deadbeef')::numeric;

Zegarek 在补充答案中指出了这一点。

Postgres 15 或更早版本

使用 `float` 作为垫脚石

我后来才意识到浮点类型（float，real）至少从那时起就支持相同的语法Postgres 9.4，可能更长：

SELECT '0xdeadbeef'::float;

我们可以将其用作垫脚石：

SELECT ('0x'||'deadbeef')::float::numeric;

简单而快速，但要警惕非常大的数字的舍入错误。 float 最多只能精确表示 15 位十进制数字。这可以可靠地转换为最多 12 个十六进制数字。准确地说，最多为十六进制 38d7ea4c67fff。除此之外，最低有效数字设置为 0。
此外，Postgres 16 语法更简单、更快。

原始答案

text 中没有十六进制数字的转换表示为数字类型，但我们可以使用 bit(n) 作为航路点。有从位字符串 (bit(n)) 到整数类型 (int2、int4、< code>int8) - 内部表示是二进制兼容的。引用汤姆·莱恩：

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

`整数` 最大。 8 个十六进制数字

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

SELECT ('x' || lpad(hex, 8, '0'))::bit(32)::int 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

`bigint` 最大。 16 个十六进制数字

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

SELECT ('x' || lpad(hex, 16, '0'))::bit(64)::bigint 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

Postgres 16 or newer

.. accepts non-decimal numeric constants (hexadecimal, octal, binary) in the form:

0xhexdigits
0ooctdigits
0bbindigits

Like other "Numeric constants", it defaults to the smallest numeric data type that won't overflow from the progression integer → bigint → numeric. So:

SELECT                0x1;  --                   1 | integer
SELECT         0x7fffffff;  --          2147483647 | integer
SELECT         0x80000000;  --          2147483648 | bigint
SELECT 0x7fffffffffffffff;  -- 9223372036854775807 | bigint
SELECT 0x8000000000000000;  -- 9223372036854775808 | numeric

This implicitly applies to the cast from text to int/bigint/numeric, so now you can:

SELECT ('0x'||'deadbeef')::numeric;

Zegarek pointed that out in an added answer.

Postgres 15 or older

Use `float` as stepping stone

I only realized later that floating point types (float,real) support the same syntax at least since Postgres 9.4, probably longer:

SELECT '0xdeadbeef'::float;

We can use that as stepping stone:

SELECT ('0x'||'deadbeef')::float::numeric;

Simple and fast, but be wary of rounding errors with very big numbers. float can only represent up 15 decimal digits precisely. That translates to up to 12 hex digits reliably. Up to hex 38d7ea4c67fff, to be precise. Beyond that, least significant digits are set to 0.
Also, Postgres 16 syntax is simpler and faster.

Original answer

There is no cast from hex numbers in text representation to a numeric type, but we can use bit(n) as waypoint. There are undocumented casts from bit strings (bit(n)) to integer types (int2, int4, int8) - the internal representation is binary compatible. Quoting Tom Lane:

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.

`integer` for max. 8 hex digits

Up to 8 hex digits can be converted to bit(32) and then coerced to integer (standard 4-byte integer):

SELECT ('x' || lpad(hex, 8, '0'))::bit(32)::int 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 uses a signed integer type, so hex numbers above '7fffffff' overflow into negative integer numbers. This is still a valid, unique representation but the meaning is different. If that matters, switch to bigint; see below.

For more than 8 hex digits the least significant characters (excess to the right) get truncated.

4 bits in a bit string encode 1 hex digit. Hex numbers of known length can be cast to the respective bit(n) directly. Alternatively, pad hex numbers of unknown length with leading zeros (0) as demonstrated and cast to bit(32). Example with 7 hex digits and int or 8 digits and bigint:

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

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

`bigint` for max. 16 hex digits

Up to 16 hex digits can be converted to bit(64) and then coerced to bigint (int8, 8-byte integer) - overflowing into negative numbers in the upper half again:

SELECT ('x' || lpad(hex, 16, '0'))::bit(64)::bigint 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

Related operations

Inverse

To convert back either integer or bigint, use the built-in (overloaded) function to_hex():

SELECT to_hex(3735928559);  -- → 'deadbeef'

`uuid` for max. 32 hex digits

The Postgres uuid data type is not a numeric type. 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 from text to uuid (no need for bit(n) as waypoint), but exactly 32 hex digits are required.

SELECT lpad(hex, 32, '0')::uuid 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

As you can see, standard output is a string of hex digits with typical separators for UUID.

md5 hash

This is particularly useful to store md5 hashes:

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

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

See:

What is the optimal data type for an MD5 field?

回复收藏 0 原文

沉默的熊 2024-12-25 12:54:52

您有两个直接问题：

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)

** 为了避免整数溢出错误中出现这样的负数，请使用 bigint 而不是 int容纳更大的十六进制数字（如 IP 地址）。

You have two immediate problems:

to_number doesn't understand hexadecimal.
X doesn't have any meaning in a to_number format string and anything without a meaning apparently means "skip a character".

I don't have an authoritative justification for (2), just empirical evidence:

=> 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, and to_timestamp, double-quoted strings skip the number of input characters contained in the string, e.g. "XX" skips two input characters.

but the behavior of non-quoted characters that are not formatting characters appears to be unspecified.

In any case, to_number isn't the right tool for converting hex to numbers, you want to say something like this:

select x'deadbeef'::int;

so perhaps this function will 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).

回复收藏 0 原文

谎言月老 2024-12-25 12:54:52

`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 将类型获取为数字。

`pg-bignum`

Internally, pg-bignum uses 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,

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.

回复收藏 0 原文

孤独难免 2024-12-25 12:54:52

这是一个使用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

Here is a version which uses numeric, so it can handle arbitrarily large hex strings:

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

回复收藏 0 原文

惯饮孤独 2024-12-25 12:54:52

如果其他人被 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;

int 版本：

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;

If anybody else is stuck with PG8.2, here is another way to do it.

bigint version:

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;

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

回复收藏 0 原文

空袭的梦i 2024-12-25 12:54:52

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

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

对于 Oracle，过滤条件为： MOD(TO_NUMBER(standard_hash(ID, 'MD5'), 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), ${processCount}) = ${processNumber}

对于 Postgres - <代码>hex_to_Decimal(MD5(ID)) % ${进程数} = ${进程数}

David Wolever's function hex_to_Decimal has a mistake.
Here is the fixed version of the function:

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$;

There are many examples of the HEX translation into Number here, but most of them have a restriction on the length of HEX.
I needed to solve the problem of a uniform distribution of identical records in different databases (Oracle and Postgres) on the "baskets".
In this case, the same records in different databases should fall into the same "baskets" of processing. The identifier of the records is string.

Oracle has an ORA_HASH function. But there is no such function in Postgres. The problem was solved by using the same md5 caching functions, which generates a 32-symbol hex long. Only the above function helped, thanks to David Wolever.

For Oracle, the filtering condition turned out to be: MOD(TO_NUMBER(standard_hash(ID, 'MD5'), 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), ${processCount}) = ${processNumber}

For Postgres - hex_to_Decimal(MD5(ID)) % ${Processcount} = ${Processnumber}

回复收藏 0 原文

二手情话 2024-12-25 12:54:52

从 PostgreSQL 16.0 开始，在其前面添加 '0x' 并表示它是一个数字：
_{demo at db<>fiddle}

select ('0x'||'123456789abcdef0123456789abcdef0123456789abcdef')::numeric;

numeric
27898229935051914142968983831921934135401027036219428335

当输入（或当没人看时注入/插值），可以跳过 || 和引号 '：

select 0x123456789abcdef0123456789abcdef0123456789abcdef;

也适用于八进制和二进制：

以下形式接受非十进制整数常量：

<前><代码>0x十六进制数字
0 八位数字
0bb数字

其中 hexdigits 是一个或多个十六进制数字（0-9、AF），octdigits 是一个或多个八进制数字 (0-7)，bindigits 是一个或多个二进制数字（0 或 1）。

Since PostgreSQL 16.0, it's enough to add '0x' in front of it and say it's a numeric:
_{demo at db<>fiddle}

select ('0x'||'123456789abcdef0123456789abcdef0123456789abcdef')::numeric;

numeric
27898229935051914142968983831921934135401027036219428335

When typing (or injecting/interpolating when noone's looking), || and quotes ' can be skipped:

select 0x123456789abcdef0123456789abcdef0123456789abcdef;

Works for octal and binary, too:

non-decimal integer constants are accepted in these forms:
0xhexdigits
0ooctdigits
0bbindigits
where hexdigits is one or more hexadecimal digits (0-9, A-F), octdigits is one or more octal digits (0-7), and bindigits is one or more binary digits (0 or 1).

回复收藏 0 原文

白芷 2024-12-25 12:54:52

这是另一种实现：

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 倍左右）。

Here is another implementation:

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$;

It is a straightforward one that works digit by digit, using the position() function to compute the numeric value of each character in the input string. Its benefit over hex_to_decimal2() is that it seems to be much faster (4x or so for md5()-generated hex strings).

回复收藏 0 原文

~没有更多了~

关于作者

风铃鹿

暂无简介

文章

26 人气

关注发私信

友情链接

文江博客

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（11）

Postgres 16 或更高版本

Postgres 15 或更早版本

使用 `float` 作为垫脚石

原始答案

`整数` 最大。 8 个十六进制数字

`bigint` 最大。 16 个十六进制数字

相关运算

逆运算

`uuid` 最大。 32 个十六进制数字

md5 哈希

Postgres 16 or newer

Postgres 15 or older

Use `float` as stepping stone

Original answer

`integer` for max. 8 hex digits

`bigint` for max. 16 hex digits

Related operations

Inverse

`uuid` for max. 32 hex digits

md5 hash

`pg-bignum`

`pg-bignum`

关于作者

相关话题

热门标签

推荐作者

尘曦

在梵高的星空下

善良天后

韬韬不绝

qq_CgiN62

不美如何

友情链接

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（11）

Postgres 16 或更高版本

Postgres 15 或更早版本

使用 float 作为垫脚石

原始答案

整数 最大。 8 个十六进制数字

bigint 最大。 16 个十六进制数字

相关运算

逆运算

uuid 最大。 32 个十六进制数字

md5 哈希

Postgres 16 or newer

Postgres 15 or older

Use float as stepping stone

Original answer

integer for max. 8 hex digits

bigint for max. 16 hex digits

Related operations

Inverse

uuid for max. 32 hex digits

md5 hash

关于作者

相关话题

热门标签

推荐作者

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

使用 `float` 作为垫脚石

`整数` 最大。 8 个十六进制数字

`bigint` 最大。 16 个十六进制数字

`uuid` 最大。 32 个十六进制数字

Use `float` as stepping stone

`integer` for max. 8 hex digits

`bigint` for max. 16 hex digits

`uuid` for max. 32 hex digits