I am trying to convert hex string to a NUMERIC column for IPV6 address
The hexadecimal input is 2001:200:101:ffff:ffff:ffff:ffff:ffff
My output should be 42540528727106952925351778646877011967
I tried the below function taken from this site by passing my input with eliminating : as 2001200101ffffffffffffffffffff
`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 + 16 ^ (i - 1) * 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;`
I am getting decimal number as
select hex_to_int('2001200101ffffffffffffffffffff');
hex_to_int
--------------------------------------
166176317495821453702777150266933247
How to get my actual decimal number?
The decimal result you have in your question is the correct result for the hexadecimal number in your question.
What you are missing are the suppressed leading zeros in the second and third IPv6 address words. You are incorrectly converting the IPv6 address string representation (2001:200:101:ffff:ffff:ffff:ffff:ffff) to the actual hexadecimal number (200102000101ffffffffffffffffffff). Notice the added zero digits. Each IPv6 word is four hexadecimal digits, but it is allowed (required by RFC 5952) to suppress leading zeroes in the words for the string representation, but that doesn't mean they are not there.
You need to make sure each IPv6 address word is four hexadecimal digits (add any missing zeroes to get to four digits, and replace any double colons with the correct number of 0000 words) before removing the colons.
There doesn't seem to be any real, legitimate reason to convert an IPv6 address to a decimal representation. IP addresses (both IPv4 and IPv6) are binary numbers, and the hexadecimal representation of IPv6 directly translates to the binary. Adding decimal into the mix is just asking for trouble.