将文本表示中的十六进制转换为十进制数 [英] Convert hex in text representation to decimal number

问题描述

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

I am trying to convert hex to decimal using PostgreSQL 9.1

使用此查询：

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

我得到以下错误：

ERROR:  invalid input syntax for type numeric: " "

我有错误吗？

推荐答案

您有两个直接的问题：

1. to_number 不懂十六进制。


2. X to_number 格式字符串，没有意义的任何内容显然意味着跳过一个字符。

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

我没有权威的理由（2），只是经验证据：

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

$ b b

文档提到双引号模式应该如何表现：

The documentation mentions how double quoted patterns are supposed to behave:

在 to_date ， to_number 和 to_timestamp 例如XX跳过两个输入字符。

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.

在任何情况下， to_number 工具，用于将十六进制转换为数字，你想这样说：

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''' || 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地址）。

** To avoid negative numbers like this from integer overflow error, use bigint instead of int to accommodate larger hex numbers (like IP addresses).

这篇关于将文本表示中的十六进制转换为十进制数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！