Need SQL Server HASHBYTES SHA1 equivalent output in snowflake

Question

I need a SQL Server equivalent Hashbytes SHA1 output from Snowflake

SQL Server:

select hashbytes('sha1',cast('214163915155286000' as varchar(18)))*1

or

select cast(hashbytes('sha1',cast('214163915155286000' as varchar(18))) as int)

SQL Server Output:

2143072043

I am able to generate hashbytes output from snowflake using below but now I am unable to convert it into numeric value

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

Partial Snowflake Output :

N0VDrFqYkK+M2GPrfJjnRn+8rys=

Expected Output from Snowflake:

2143072043

FYI - I have tried SQL Server Code here

http://sqlfiddle.com/#!18/9eecb/150528

This has previously been answered [here](https://stackoverflow.com/questions/70668869/need-sql-server-hashbytes-sha1-output-using-snowflake-sha1-standard-sha1-functio/70669502#70669502) — NickW, Jan 12 '22 at 09:09
@NickW I know but I need to convert the char value to SQL server equivalent output as Integer — Beginner, Jan 12 '22 at 09:17
As I see, the previous answer of mine was totally correct as it gives the expected result. So why don't you mark it as correct? Second thing, if you had asked this question like this, I would probably find a solution yesterday, but now I see that I spent time on unnecessary conversion. Next time, please ask questions in more detail. — Gokhan Atil, Jan 12 '22 at 11:52
@GokhanAtil Your solution was totally correct. I didn't know that the output for snowflake will be in the string which could not be converted directly into an Integer like SQL Server. Your solution definitely helped me next time I will ask question in more detail — Beginner, Jan 12 '22 at 13:19
but a "base64 encoded binary" is a string. What does it mean to cast a string to an int, this seems like a useless SQL Server feature, a HASH is useful, a base64 of a hash is useful, what is a "int of a string". Really you should ask a question of the SQL Server people, say "what is really happening when a turn a string into a int" and then do the same butchery. — Simeon Pilgrim, Jan 12 '22 at 20:36

Simeon Pilgrim · Answer 1 · 2022-01-14T08:18:13.090

1

So doing a small amount of playing in SQL Server.

select '214163915155286000' as s,
    hashbytes('sha1', '214163915155286000') as h,
    cast(hashbytes('sha1', '214163915155286000') as int) as i
;

Gives:

s, 214163915155286000
h, 0x374543AC5A9890AF8CD863EB7C98E7467FBCAF2B
i, 2143072043

so my SQL is the same as your, as good start.

converting 2143072043 to hex we get 7FBCAF2B which is the last 4 bytes of the hash.

Thus the you want to get the result of hash and truncate, which I will BITAND to achieve, but last time I used the snowflake BIT functions they did allow hex input, so in stead of type a rather clear 0xFFffFFff we will use the decimal of that 4294967295, thus this should work for you:

select bitand(sha1('214163915155286000'), 4294967295);

Right so thanks to Gokhan's insights, and logging into snowflake, and reading the manual for BITSHIFTLEFT/RIGHT. we can use the shifts, but the output is a 128 bit number, not a 64 bit, as I had assumed, to extend the sign bit correctly we have to shift by 96 bits, which this code shows working:

SELECT  
    column1 as input,
    sha1(input) as sha1,
    right(sha1,8) as right8,
    to_number(right8,'XXXXXXXX') as int32,
    BITSHIFTRIGHT(BITSHIFTLEFT(int32,96),96) as result
FROM VALUES 
    ('214163915155286001'),
    ('214163915155286000')
ORDER BY 1;

which gives the output:

INPUT	SHA1	RIGHT8	INT32	RESULT
214163915155286000	374543ac5a9890af8cd863eb7c98e7467fbcaf2b	7fbcaf2b	2143072043	2143072043
214163915155286001	1911d3df794846fbc74e0e4cf29133459466e0e7	9466e0e7	2489770215	-1805197081

so a more compact and final block of SQL can be:

BITSHIFTRIGHT(BITSHIFTLEFT(to_number(right(sha1(input),8),'XXXXXXXX'),96),96)

edited Jan 14 '22 at 08:18

answered Jan 12 '22 at 20:54

Simeon Pilgrim

22,906
3
32
45

I also noticed the last 8 digits/4 bytes but I didn't share my solution because it was not working as expected! Actually, yours do not work as expected either. Unfortunately, I couldn't show it because the SQL Server instance on SQLFiddle doesn't work now... If it starts to work tomorrow, I will post my answer. :) – Gokhan Atil Jan 12 '22 at 22:03
@GokhanAtil are you saying `select bitand(sha1('214163915155286000'), 4294967295);` is not working in snowflake, to be honest, I did run it. but it should work.. if I add the extra paren – Simeon Pilgrim Jan 13 '22 at 00:37
1

Yes, it doesn't work (gives numeric value is not recognized error) but there is also a missing logic which I will explain in my response. – Gokhan Atil Jan 13 '22 at 08:24
@SimeonPilgrim Thanks for explaining the root cause. I got the real problem now – Beginner Jan 13 '22 at 10:06
1

@GokhanAtil I don't have snowflake access presently, so cannot test, but if your are wanting in it the correct sign extended there is a function for that too `BITSHIFTRIGHT` https://docs.snowflake.com/en/sql-reference/functions/bitshiftright.html – Simeon Pilgrim Jan 13 '22 at 19:55
Ah yes, thanks. I think I can use it to make the UDF more readable! – Gokhan Atil Jan 13 '22 at 21:17
Oh nop, it doesn't help as I don't need to shift but I need AND with some masks. So it seems current version is fine – Gokhan Atil Jan 13 '22 at 21:21
rightshifting by 32 is the same as a mask and OR the high bits back in.. – Simeon Pilgrim Jan 13 '22 at 21:59

Gokhan Atil · Accepted Answer · 2022-01-13T10:08:30.403

When I examined your post yesterday, I noticed that Ms SQL Server uses the last 8 digits of the SHA1 Hash (as Simon pointed), so I wrote this query:

select to_number(right( sha1('214163915155286000'), 8 ), 'XXXXXXXX' );

It produces 2143072043 as expected, but unfortunately, this is not enough if we want to simulate the behavior of Ms SQL Server.

If you convert '214163915155286001', the above conversion returns 2489770215. If we do the exact conversion in Ms SQL Server, it returns -1805197081. The problem is, Ms SQL Server, stores the result in a "signed integer". In Snowflake, there is no signed integer. The integer is synonymous with NUMBER. So we need to handle the "signed" bit. This is why I wrote the following UDF:

create or replace function convertMStoSF( SHA varchar  )
returns NUMBER
language SQL
as
$$
IFF( bitand( to_number(right( sha1( SHA ), 8 ), 'XXXXXXXX' ) , 2147483648 ) > 0,  
       - 2147483648 + bitand( 2147483647,  to_number(right( sha1( SHA ), 8 ), 'XXXXXXXX' )  ) ,
       to_number(right( sha1( SHA ), 8 ), 'XXXXXXXX' ) )
$$;

It gets the last 8 digits, converts to a number, and checks the signed bit. If it's 1, then it calculates the signed value. If it's 0, it returns the number directly. In the UDF, I used the decimal representation instead of the hexadecimal:

0x7FFFFFFF -> 2147483647
0x80000000 -> 2147483648

Here is a query to test the results:

select '214163915155286000' A, convertMStoSF( A ), '214163915155286001' B, convertMStoSF( B );

+--------------------+--------------------+--------------------+--------------------+
|         A          | CONVERTMSTOSF( A ) |         B          | CONVERTMSTOSF( B ) |
+--------------------+--------------------+--------------------+--------------------+
| 214163915155286000 |         2143072043 | 214163915155286001 |        -1805197081 |
+--------------------+--------------------+--------------------+--------------------+

I have to say that I'm not sure if it's 100% correct, as I do my test with only a few sample numbers.

Man you responded right on time. I was literally struggling to fix the -ve sign value. Thanks a lot! — Beginner, Jan 13 '22 at 10:08

Need SQL Server HASHBYTES SHA1 equivalent output in snowflake

2 Answers2