Need SQL Server HASHBYTES SHA1 output using Snowflake SHA1/standard SHA1 function

Question

We are migrating SQL server commands/scripts to Snowflake SQL and got stuck with this particular query. We have been using the HASHBYTES function in SQL Server for hashing this string to the SHA1 algorithm. Unfortunately, the output of our SQL server command is not matching with the snowflake or any other converter.

SQL Server Code:

select hashbytes('sha1',cast('214163915155286001' as varchar(18)))

SQL Server Output:

"GRHT33lIRvvHTg5M8pEzRZRm4Oc="

We tried writing the above code in snowflake as below:

Snowflake SQL:

Select sha1('214163915155286001')

Snowflake Output:

"1911d3df794846fbc74e0e4cf29133459466e0e7"

We are getting the same output with any other standard SHA1 converter but now we need exactly the same value as we were getting from SQL server HASHBYTES.

Answer 1

You can use the following conversion to get the same result in Snowflake:

select to_char(to_binary(sha1('214163915155286001'), 'hex'), 'base64') as Result;

+------------------------------+
|            RESULT            |
+------------------------------+
| GRHT33lIRvvHTg5M8pEzRZRm4Oc= |
+------------------------------+

It's a kind of Magic :)

Answer 2

Gokhan's answer is the howget the answer you say you want. But understanding what you have might help,

"1911d3df794846fbc74e0e4cf29133459466e0e7" is the SHA1 hash, as presented as a string of the hash. Which you would use if you are doing a comparison of data SHA1 to check things are the "same and not changed" (to the limits of SHA1's ability to confirm this).

"GRHT33lIRvvHTg5M8pEzRZRm4Oc=" is a BASE64 encoded string. The normal giveaway is the equals on the end =. But those are not always there as base64 encodes blocks of 3 x 2^8 (256) binary data as 4 x 2^6 (64) stream in the printable token range.