PostgreSQL: convert a very large number hexadecimal string to NUMERIC

Question

I am trying to convert a hexadecimal string of a very large number to a NUMERIC column

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS NUMERIC AS $$ DECLARE result NUMERIC; BEGIN EXECUTE 'SELECT x''' || hexval || '''::NUMERIC(40,0)' INTO result; RETURN result; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; 

There I try to do this:

 select hex_to_int(tx.value) from internal_transaction tx 

The error I am getting is:

 [42846] ERROR: cannot cast type bit to numeric Where: PL/pgSQL function hex_to_int(character varying) line 5 at EXECUTE statement 

Answer 1

This is a kind of brute force, not a bulletproof one:

 CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS numeric AS $$ DECLARE result NUMERIC; i integer; len integer; hexchar varchar; BEGIN result := 0; len := length(hexval); for i in 1..len loop hexchar := substr(hexval, len - i + 1, 1); result := result + round(16 ^ (i - 1)::dec * case when hexchar between '0' and '9' then cast (hexchar as int) when upper (hexchar) between 'A' and 'F' then ascii(upper(hexchar)) - 55 end); end loop; RETURN result; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; select hex_to_int('12AE34F'); -- returns 19587919 

Or, if you have PL / Perl installed, you can let it do the hard work:

 CREATE OR REPLACE FUNCTION hex_to_int_perl(varchar) RETURNS numeric AS $BODY$ my ($hex) = @_; return sprintf "%d", hex($hex); $BODY$ LANGUAGE plperl VOLATILE COST 100; select hex_to_int_perl('12AE34F'); -- returns 19587919 

I don’t think that non-Perl alone works with negative numbers, and I’m pretty sure that both will give you bad results if you enter a non-hexadecimal value, but this will be a fairly simple script to capture and process, depending on what do you want the function to do.

Answer 2

My use case for this was to convert hexadecimal SHA-1 hash values ​​to integers. Without attention to numerical accuracy in the entire function, this use case easily reveals flaws; but of course, use cases with an even larger "very large number" are easy to identify.

Given some SHA-1 hashes, the first of the response decisions made led to the following:

 SELECT hex_to_int('356e90d2a2d414ba8757ec2ab91f2f19c481d4c3'); -- returns 305042208670409000000000000000000000000000000000 SELECT hex_to_int('aaa9f7193cc8efe7e98145b0f8d9ae5f1712c25b'); -- returns 974318782301086000000000000000000000000000000000 

Of course, every result that has the correct location with so many zeros is a sign of inadequate accuracy for use.

This is where I ended up to get the accuracy I need:

 CREATE OR REPLACE FUNCTION hex_to_int(hexVal varchar) RETURNS numeric(1000) AS $$ DECLARE intVal numeric(1000) := 0; hexLength integer; i integer; hexDigit varchar; BEGIN hexLength := length(hexVal); FOR i IN 1..hexLength LOOP hexDigit := substr(hexVal, hexLength - i + 1, 1); intVal := intVal + CASE WHEN hexDigit BETWEEN '0' AND '9' THEN CAST(hexDigit AS numeric(1000)) WHEN upper(hexDigit) BETWEEN 'A' AND 'F' THEN CAST(ascii(upper(hexDigit)) - 55 AS numeric(1000)) END * CAST(16 AS numeric(1000)) ^ CAST(i - 1 AS numeric(1000)); END LOOP; RETURN intVal; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; SELECT hex_to_int('356e90d2a2d414ba8757ec2ab91f2f19c481d4c3'); -- returns 305042208670409212880202819376501392142011323587 SELECT hex_to_int('aaa9f7193cc8efe7e98145b0f8d9ae5f1712c25b'); -- returns 974318782301085717223606317572453925850501530203 

Answer 3

Another conversion function. The idea is to reduce the number of steps in the loop and therefore the number of arithmetic operations.

 create or replace function hex_to_numeric(str text) returns numeric language plpgsql immutable strict as $$ declare i int; n int = length(str)/ 8; res dec = 0; begin str := lpad($1, (n+ 1)* 8, '0'); for i in 0..n loop if i > 0 then res:= res * 4294967296; end if; res:= res + concat('x', substr(str, i* 8+ 1, 8))::bit(32)::bigint::dec; end loop; return res; end $$; 

Some tests:

 select hex, hex_to_numeric(hex) from ( values ('ff'::text), ('7fffffff'), ('80000000'), ('deadbeef'), ('7fffffffffffffff'), ('8000000000000000'), ('ffffffffffffffff'), ('ffffffffffffffff123'), ('4540a085e7334d6494dd6a7378c579f6') ) t(hex); hex | hex_to_numeric ----------------------------------+---------------------------------------- ff | 255 7fffffff | 2147483647 80000000 | 2147483648 deadbeef | 3735928559 7fffffffffffffff | 9223372036854775807 8000000000000000 | 9223372036854775808 ffffffffffffffff | 18446744073709551615 ffffffffffffffff123 | 75557863725914323415331 4540a085e7334d6494dd6a7378c579f6 | 92052294502540680826164862654582454774 (9 rows)