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 :)