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.

I ran your code in SQL Server 2019 : I get 0x1911D3DF794846FBC74E0E4CF29133459466E0E7 as expected. This value "GRHT33lIRvvHTg5M8pEzRZRm4Oc=" looks like a base64 encoded value — Mitch Wheat, Jan 11 '22 at 15:05
@MitchWheat.. check here http://sqlfiddle.com/#!18/9eecb/150431 — Beginner, Jan 11 '22 at 15:07
I don't need to, I have an actual SQL Server 2019 instance. And the value you get is (as I said) 0x1911D3DF794846FBC74E0E4CF29133459466E0E7 — Mitch Wheat, Jan 11 '22 at 15:10
@MitchWheat I am not getting the same value with MS SQL Server 2017 and our classic system is too returning the value "GRHT33lIRvvHTg5M8pEzRZRm4Oc=" — Beginner, Jan 11 '22 at 15:14

score 2 · Accepted Answer · answered Jan 11 '22 at 15:40

2

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

answered Jan 11 '22 at 15:40

Gokhan Atil

9,278
2
11
24

Thanks! Is there any way to convert the output to an integer? The final output of the sql server is an integer. select hashbytes('sha1',cast('214163915155286000' as varchar(18)))*1 output: 2143072043 – Beginner Jan 11 '22 at 20:00
1

I will check it – Gokhan Atil Jan 12 '22 at 08:47

score 1 · Answer 2 · answered Jan 12 '22 at 20:43

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.

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

2 Answers2

Linked