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:

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?

Java: `Integer.parseUnsignedInt("F933F177", 16) / 1_000_000.0` or `Integer.parseUnsignedInt("F933F177", 16) / 1E6` — user16320675, Apr 18 '22 at 18:04
Java uses signed 2's complement, so no need to convert, the solution of friend above will works fine. — Kaneda, Apr 18 '22 at 18:14

score 1 · Accepted Answer · answered Apr 18 '22 at 18:15

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:

answered Apr 18 '22 at 18:15

Lukasz Szozda

162,964
23
234
275

For fun (and potential scalability) check the SQL alternative https://stackoverflow.com/a/71917637/132438 – Felipe Hoffa Apr 18 '22 at 22:20

score 1 · Answer 2 · answered Apr 18 '22 at 22:19

1

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

answered Apr 18 '22 at 22:19

Felipe Hoffa

54,922
16
151
325

Based on https://stackoverflow.com/a/34679269/132438 – Felipe Hoffa Apr 18 '22 at 22:21
To avoid writing the 'XXXXXX' string by hand, you can `repeat('X', l)` – Felipe Hoffa Apr 19 '22 at 02:59
1

As always, SQL can do it too :D – Lukasz Szozda Apr 19 '22 at 05:29

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

2 Answers2