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

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.

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

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