PostgreSQL: convert hex string of a very large number to a NUMERIC

Question

I am trying to convert hex 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 am trying to do this:

select hex_to_int(tx.value) from internal_transaction tx

The error I get is :

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

Related: https://stackoverflow.com/q/66226614/939860, https://stackoverflow.com/q/8316164/939860 — Erwin Brandstetter, Mar 29 '21 at 01:58

klin · Answer 1 · 2022-09-23T13:34:16.780

Yet another conversion function. The idea is to reduce the number of steps in the loop, and hence 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
        res:= res * 4294967296; -- hex 100000000
        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)

The function is 5-10 times faster than the plpgsql functions in the other answers (depending on the length of hex values).

score 5 · Accepted Answer · edited Jan 10 '19 at 13:56

This is sort of brute force and not at all bulletproof:

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 heavy lifting:

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 the non-Perl one works with negative numbers, and I'm quite sure both will give you bad results if you put in a non-hex value, but those would be easy enough scenarios to trap and handle, depending on what you want the function to do.

The non-perl function did not work well for input longer than 16 chars. — klin, Jan 10 '19 at 13:56

score 4 · Answer 3 · answered Jun 28 '17 at 08:34

My use case for this was converting hexadecimal SHA-1 hash values to integers. Without attention to numeric precision throughout the function, this use case easily reveals shortcomings; but certainly use cases with even bigger "very large number"s are easy to identify.

Given some SHA-1 hashes, the first of the accepted answer's solutions yielded the following:

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

SELECT hex_to_int('aaa9f7193cc8efe7e98145b0f8d9ae5f1712c25b');
-- returns 974318782301086000000000000000000000000000000000

Of course each result being right-padded with so many zeros is a sign of inadequate precision for the use case.

Here is where I ended up to get the precision I needed:

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

PostgreSQL: convert hex string of a very large number to a NUMERIC

3 Answers3

Linked