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: