'How to convert a hash string to an integer in Snowflake?

I'm trying to get a hash of a decimal value and convert it to an integer. But the query results in the following error:

Numeric value 'b902cc4550838229a710bfec4c38cbc7eb11082367a409df9135e7f007a96bda' is not recognized

SELECT (CAST(sha2(TO_VARCHAR(ABS(12.5)), 256) AS INTEGER) % 100) AS temp_value

What is the correct way to convert a hash string to an integer in Snowflake? I can not use any user defined functions. And have to go with Snowflake native functions.

sqlhashsnowflake-cloud-data-platform


Solution 1:[1]

The hash value contains alphabetic character so it will throw an error

SELECT --(CAST(
sha2(
TO_VARCHAR(
  ABS(12.5)), 256)-- AS INTEGER) % 100) 
  AS temp_value;

Solution 2:[2]

You need to convert the hex value from the hash encoding to be int.

I've not been able to find a function built into Snowflake that does this, but if you have a look in the following link, it will explain how to create a javascript function to do the conversion for you: https://snowflakecommunity.force.com/s/article/faq-does-snowflake-have-a-hex-to-int-type-function

If you use the function in the link, then your code becomes something like this:

SELECT (CAST(js_hextoint(sha2(TO_VARCHAR(ABS(12.5)), 256)) AS INTEGER) % 100) AS temp_value

I've not been able to test the above code I'm afraid, so there may be a bracket in the wrong place...

Solution 3:[3]

You have a 56 digit hexadecimal number. It's not going to fit into the maximum numeric precision of 38. You could use a floating point number, but that will lose precision.

create or replace function CONV(VALUE_IN string, OLD_BASE float, NEW_BASE float)
returns string
language javascript
as
$$
    // Usage note: Loses precision for very large inputs
    return parseInt(VALUE_IN, Math.floor(OLD_BASE).toString(Math.floor(NEW_BASE)));
$$;

select conv('b902cc4550838229a710bfec4c38cbc7eb11082367a409df9135e7f007a96bda', 16, 10);

--Returns 8.368282050700398e+76

Solution 4:[4]

For hex to integer, check Does snowflake have a ‘hex’ to ‘int’ type native function?. My guess is that most people checking this question (1k views) are looking for that.

But this specific question wants to convert a sha2 digest to integer for comparison purposes. My advice for that specific question is "don't".

That's because the hex string in the question represent the integer 83682820507003986697271120393377917644380327831689630185856829040117055843290, which is too much to handle even by Java's BigInteger.

Instead, just compare strings/binary to check if the values match or not.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Iqra Ijaz
Solution 2 Ben K
Solution 3 Greg Pavlik
Solution 4 Felipe Hoffa

Related Questions