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

Question

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:

1. Convert hex to binary.
2. Convert binary to signed 2's complement (effectively reversing the signed 2's complement).
3. Convert to decimal
4. 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?

Answer 1

Wrapping the code from user16320675's comment using inline JAVA UDF:

create function transform_number(num varchar)
returns double
language java
handler='Test.transform_number'
target_path='@~/Test.jar'
as
$$
    class Test {
        public static double transform_number(String num) {
          return Integer.parseUnsignedInt(num, 16) / 1_000_000.0;
        }
    }
$$;

Function call:

SELECT transform_number('F933F177') AS result;

Output:

Answer 2

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 
)
$$
;