SELECT ('x' || lpad('1f', 8, '0'))::bit(32)::int; --> 31
SELECT ('x' || lpad('1f', 16, '0'))::bit(64)::bigint; --> 31
create function hex_to_decimal(hex_string text)
returns text
language plpgsql immutable as $pgsql$
declare
bits bit varying;
result numeric := 0;
exponent numeric := 0;
chunk_size integer := 31;
start integer;
begin
execute 'SELECT x' || quote_literal(hex_string) INTO bits;
while length(bits) > 0 loop
start := greatest(1, length(bits) - chunk_size);
result := result + (substring(bits from start for chunk_size)::bigint)::numeric * pow(2::numeric, exponent);
exponent := exponent + chunk_size;
bits := substring(bits from 1 for greatest(0, length(bits) - chunk_size));
end loop;
return trunc(result, 0);
end
$pgsql$;SELECT hex_to_decimal('1f'); --> 31
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;
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;SELECT hex_to_bigint('1f'); --> 31
SELECT hex_to_int('1f'); --> 31
Проверено на Postgresql ≥ 10 и выше.
… источник тут