Converti esadecimale in rappresentazione testuale in numero decimale

Sto cercando di convertire hex in decimale usando PostgreSQL 9.1

con questa query:

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

Ottengo il seguente errore:

 ERROR: invalid input syntax for type numeric: " " 

Che cosa sto facendo di sbagliato?

Hai due problemi immediati:

  1. to_number non capisce esadecimale.
  2. X non ha alcun significato in una stringa di formato to_number e qualsiasi cosa senza un significato apparentemente significa “salta un personaggio”.

Non ho una giustificazione autorevole per (2), solo prove empiriche:

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

La documentazione menziona come si suppone che si comportino i doppi schemi citati:

In to_date , to_number e to_number , le stringhe con doppia quotatura salta il numero di caratteri di input contenuti nella stringa, ad esempio "XX" salta due caratteri di input.

ma il comportamento dei caratteri non quotati che non sono caratteri di formattazione sembra non specificato.

In ogni caso, to_number non è lo strumento giusto per convertire esadecimali, vuoi dire qualcosa di simile a questo:

 select x'deadbeef'::int; 

quindi forse questa funzione funzionerà meglio per te:

 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; 

Poi:

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

** Per evitare numeri negativi come questo dall’errore di overflow di interi, utilizzare bigint invece di int per ospitare numeri esadecimali più grandi (come gli indirizzi IP).

Ci sono modi senza SQL dinamico .

Max. 8 cifre esadecimali

Non c’è cast dai numeri esadecimali nella rappresentazione del text in un tipo numerico, ma possiamo usare bit(n) come waypoint. 4 bit in una stringa di bit codificano 1 cifra esadecimale. Esiste un cast non documentato da bit string fino a bit(32) (massimo 8 cifre esadecimali) a integer (standard integer 4 byte) – la rappresentazione interna è compatibile con i binari.

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

Risultato:

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

4 byte sono sufficienti per codificare tutti i numeri esadecimali fino a 8 cifre, ma il integer in Postgres è un tipo firmato, quindi i numeri esadecimali sopra '7fffffff' overflow '7fffffff' in un numero int negativo . Questa è ancora una rappresentazione unica, ma il significato è diverso. Se è importante passare a bigint , vedi sotto.

Per numeri esadecimali di lunghezza variabile sconosciuta, è necessario eseguire il pad degli zeri iniziali 0 come dimostrato per eseguire il cast a bit(32) . Per numeri di lunghezza nota possiamo semplicemente adattare l’identificatore di lunghezza. Esempio con 7 cifre esadecimali e int o 8 cifre e bigint :

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

Max. 16 cifre esadecimali

Usa bigint ( int8 , numero intero a 8 byte) per un massimo di 16 cifre esadecimali – traboccante di numeri negativi nella metà superiore:

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

Risultato:

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

Per più di 16 cifre esadecimali i caratteri meno significativi (in eccesso a destra) vengono troncati .

Questo cast si basa su comportamenti non documentati , cito Tom Lane qui :

Ciò si basa su alcuni comportamenti non documentati del convertitore di input di tipo bit, ma non vedo alcun motivo per aspettarsi che si interrompa. Un problema forse più grande è che richiede PG> = 8.3 poiché prima non c’era un testo da castare.

UUID per max. 32 cifre esadecimali

Il tipo di dati uuid Postgres non è un tipo numerico , quindi questo si discosta dalla domanda posta. Ma è il tipo più efficiente in Postgres standard per memorizzare fino a 32 cifre esadecimali, occupando solo 16 byte di spazio. Esiste un cast diretto , ma sono richieste esattamente 32 cifre esadecimali.

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

Risultato:

  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 

Come puoi vedere, l’output standard è una stringa di cifre esadecimali con separatori tipici per UUID.

md5 hash

Questo è particolarmente utile per memorizzare gli hash MD5 :

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

Risultato:

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

pg-bignum

Internamente, pg-bignum usa la libreria SSL per grandi numeri. Questo metodo non ha nessuno degli inconvenienti menzionati nelle altre risposte con valori numerici. Né è rallentato da plpgsql. È veloce e funziona con un numero di qualsiasi dimensione. Test case preso dalla risposta di Erwin per il confronto,

 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) 

Puoi ottenere il tipo in numerico usando bn_in_hex('deadbeef')::text::numeric .

Se qualcun altro è bloccato con PG8.2, ecco un altro modo per farlo.

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

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