I am converting Decimal number
42541956123769884636017138956568135808
to hexadecimal number
20014860486000000000000000008880
My converted value is 20014860486000000000000000019980
My Postgresql function for conversion is
CREATE OR REPLACE FUNCTION ipv6_dec_hex(ip_number numeric)
RETURNS character varying AS
$BODY$
DECLARE
ip int :=null;
ip1 character varying := '';
ip2 character varying;
BEGIN
while(ip_number != 0)
LOOP
ip = trunc(mod(ip_number,16));
ip_number = trunc(ip_number/16);
ip1:=ip1||to_hex(ip);
raise notice 'ip is %',ip;
raise notice 'ip_number is %',ip_number;
END LOOP;
ip2:=reverse(ip1);
return ip2;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
I am using modulo-remainder method. so I am taking modulus for the given number with 16 and divide given decimal number by 16 for consecutive hexadecimal number conversions. While taking modulus in first iteration I am getting
0as remainder as last digit of hexadecimal number20014860486000000000000000008880and next dividend is
2658872257735617789751071184785508488
In second iteration taking modulus of 2658872257735617789751071184785508488 getting
8as remainder second from last digit of hexadecimal number20014860486000000000000000008880and next dividend is
166179516108476111859441949049094281
In third iteration taking modulus of 166179516108476111859441949049094281 getting
9as remainder but8is the exact hexadecimal digit to come in third from last digit of hexadecimal number20014860486000000000000000008880
If the above dividend value is 166179516108476111859441949049094280
instead of 166179516108476111859441949049094281 then i will get the remainder value as 8 . How could i get the exact hexadecimal value 20014860486000000000000000008880 instead of 20014860486000000000000000019980
1 Answer
If you say this you'll get floating-point division, and that will give a loss of precision.
ip_number = trunc(ip_number/16);
Say this instead
ip_number = div(ip_number,16);
full source after edit:
CREATE OR REPLACE FUNCTION ipv6_dec_hex(ip_number numeric)
RETURNS character varying AS
$BODY$
DECLARE
ip int :=null;
ip1 character varying := '';
ip2 character varying;
BEGIN
while(ip_number != 0)
LOOP
ip = mod(ip_number,16);
ip_number = div(ip_number,16);
ip1:=ip1||to_hex(ip);
raise notice 'ip is %',ip;
raise notice 'ip_number is %',ip_number;
END LOOP;
ip2:=reverse(ip1);
return ip2;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
select ipv6_dec_hex(42541956123769884636017138956568135808)
='20014860486000000000000000008880';
