Converter hex em representação de texto para o número decimal

Eu estou tentando converter hexadecimal para decimal usando o PostgreSQL 9.1

com esta consulta:

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

Estou tendo o erro a seguir:

 ERROR: invalid input syntax for type numeric: " " 

O que estou fazendo de errado?

Você tem dois problemas imediatos:

  1. to_number não entende hexadecimal.
  2. X não tem nenhum significado em uma string de formato to_number e qualquer coisa sem significado aparentemente significa “pular um caractere”.

Eu não tenho uma justificativa autoritária para (2), apenas evidências empíricas:

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

A documentação menciona como padrões duplos citados devem se comportar:

Em to_date , to_number e to_timestamp , strings com aspas duplas ignoram o número de caracteres de input contidos na string, por exemplo, "XX" pula dois caracteres de input.

mas o comportamento de caracteres não citados que não estão formatando caracteres parece não ser especificado.

Em qualquer caso, to_number não é a ferramenta certa para converter hexadecimal em números, você quer dizer algo assim:

 select x'deadbeef'::int; 

então talvez essa function funcione melhor para você:

 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; 

Então:

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

** Para evitar números negativos como este de erro de estouro de número inteiro, use bigint em vez de int para acomodar números hexadecimais maiores (como endereços IP).

Existem maneiras sem SQL dynamic .

Max. 8 dígitos hexadecimais

Não há conversão de números hexadecimais em representação de text para um tipo numérico, mas podemos usar bit(n) como um waypoint. 4 bits em uma string de bits codificam 1 dígito hexadecimal. Existe uma conversão não documentada de strings de bit até o bit(32) (no máximo 8 dígitos hexadecimais) para o integer ( integer padrão de 4 bytes) – a representação interna é binária compatível.

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

Resultado:

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

4 bytes são suficientes para codificar todos os números hexadecimais de até 8 dígitos, mas o integer no Postgres é um tipo assinado, então os números hexadecimais acima de '7fffffff' em um número int negativo . Esta ainda é uma representação única, mas o significado é diferente. Se isso importa mudar para bigint , veja abaixo.

Para números hexadecimais de comprimento variável desconhecido, precisamos preencher os zeros à esquerda 0 como demonstrado para converter em bit(32) . Para números de comprimento conhecido, podemos apenas adaptar o especificador de comprimento. Exemplo com 7 dígitos hexadecimais e int ou 8 dígitos e bigint :

 SELECT ('x'|| 'deafbee')::bit(28)::int , ('x'|| 'deadbeef')::bit(32)::bigint; int4 | int8 -----------+------------ 233503726 | 3735928559 

Max. 16 dígitos hexadecimais

Use bigint ( int8 , int8 8 bytes) para até 16 dígitos hexadecimais – transbordando para números negativos na metade superior:

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

Resultado:

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

Para mais de 16 dígitos hexadecimais, os caracteres menos significativos (excesso à direita) são truncados .

Este casting confia no comportamento indocumentado , cito Tom Lane aqui :

Isso depende de algum comportamento não documentado do conversor de input do tipo de bit, mas não vejo razão para esperar que isso seja interrompido. Um problema possivelmente maior é que ele requer PG> = 8.3, já que não havia um texto a ser lançado antes disso.

UUID para max. 32 dígitos hexadecimais

O tipo de dados do Postgres uuid não é um tipo numérico , portanto, isso se desvia da pergunta feita. Mas é o tipo mais eficiente no Postgres padrão para armazenar até 32 dígitos hexadecimais, ocupando apenas 16 bytes de armazenamento. Existe uma conversão direta , mas exatamente 32 dígitos hexadecimais são necessários.

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

Resultado:

  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 

Como você pode ver, a saída padrão é uma seqüência de dígitos hexadecimais com separadores típicos para o UUID.

md5 hash

Isso é particularmente útil para armazenar hashes md5 :

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

Resultado:

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

pg-bignum

Internamente, o pg-bignum usa a biblioteca SSL para grandes números. Esse método não tem nenhum dos inconvenientes mencionados nas outras respostas com o numérico. Nem é retardado por plpgsql. É rápido e funciona com vários tamanhos. Caso de teste tirado da resposta de Erwin para comparação,

 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) 

Você pode obter o tipo numérico usando bn_in_hex('deadbeef')::text::numeric .

Se alguém mais estiver preso ao PG8.2, aqui está outra maneira de fazer isso.

versão 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; 

versão 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;