Convert from signed integers to Hex

[williey]

I have a column containing signed integers that need to be converted to Hex and reverse the hex show the IP address..
I have tried using the RAWTOHEX function but I do not get the expected result.
Here are the steps for the conversion to take place..

1. Convert the database display (-1139627840) to hex value. 0xBC12A8C0.
2. Reverse the hex bytes, as shown below.: C0 A8 12 BC
3. Convert the bytes from hex to decimal as shown: 192 168 18 188
4. The IP address displays in the following format: 192.168.18.188

 

[jaggiebunnet]

Have you tried using to_char?

jaggie@DEV>SELECT TO_CHAR(1139627840, 'xxxxxxxx') from dual;

TO_CHAR(1
---------
 43ed5740

Also, just wondering, where do you get your value there from after you convert it to hex(BC12A8C0)? I have double checked this result in calculators and they agree with the result.

 

[williey]

You are missing the -ve sign and that is why the results are different. Are you using a different NLS setting? I tried it with the -ve value and I get "##########" returned to me..

 

[SantaMufasa]

Williey,

I agree with Jaggie...your Rad(10)->Rad(16) values just don't correlate. Here are the values I get from my base-conversion function:

select baseconv('-1139627840',10,16) from dual;

BASECONV('-1139627840',10,16)
-----------------------------
-43ED5740 (<- hex equivalent of -1139627840 base 10)

select baseconv('BC12A8C0',16,10) from dual;

BASECONV('BC12A8C0',16,10)
--------------------------
3155339456 (<- base 10 equivalent of BC12A8C0 hex)

Once we agree on conversion values, I'm certain that we can produce a function that returns an xxx.xxx.xxx.xxx-format IP address from whatever input value you choose. BTW, is it not correct that whether your input is positive or negative, it appears to make no difference to your IP-style output, right?

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:16 (07Aug04) UTC (aka "GMT" and "Zulu"), 17:16 (06Aug04) Mountain Time)

 

[williey]

The numbers do correlate. I had verified this using the scientific calculator by punching in the decimal values and hitting the hex conversion button..

 

[SantaMufasa]

It appears that we need a fourth (or fifth) opinion for Williey. Is someone out there willing to break the stalemate and confirm whether Jaggie and I are right ('-1139627840' base 10 = '-43ED5740' base 16) or whether Williey's scientific calculator is right ('-1139627840' base 10 = '-BC12A8C0' base 16)?

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:23 (07Aug04) UTC (aka "GMT" and "Zulu"), 23:23 (06Aug04) Mountain Time)