How Can I Convert Hex 'F933F177' to Decimal -114.036361 via SnowSQL or a Java function?-CodePudding

I have coordinates stored in HEX, which from searching online appear to have used Signed 2's Complement to handle the negative values. I'm getting a bit lost with where the various conversions are made, I think the path should be:

Convert hex to binary.
Convert binary to signed 2's complement (effectively reversing the signed 2's complement).
Convert to decimal
Divide by 1,000,000

How far off am I with this process?

I'm working with Snowflake, so I can use SnowSQL or a Java junction to get the desired result. I am new to looking at hex and signed 2's complement.

How can I reverse engineer hex value F933F177 to get decimal value -114.036361?

CodePudding user response：

Wrapping the code from

CodePudding user response：

I like Lukasz Java answer - it's straightforward.

Here you have a pure SQL answer that you can use if you want pure SQL - also it helps to understand what's the process to get this transformation done:

select 'F933F177' s
    , to_number(s,'XXXXXXXX') n
    , length(s) l
    , pow(2, l/2*8) maxn
    , iff(n>maxn/2-1, n-maxn, n) n2
    , n2/1000000 n3

As a SQL UDF:

create or replace function signed_two_compliment(s varchar)
returns float
as $$
select n3 
from (
    select to_number(s,'XXXXXXXX') n
        , length(s) l
        , pow(2, l/2*8) maxn
        , iff(n>maxn/2-1, n-maxn, n) n2
        , n2/1000000 n3 
)
$$
;