Bigquery use hash sha1 to convert nvarchar data

Google Cloud Data Analytics
1

Hi,

In SQL Server, the value will be different when using HASHBYTES to compute hash of sha1 in differnet types between varchar and nvarchar. I try to use Bigquery to compute hash of sha1, but the compute result is only same when data type is varchar in SQL server. If I want to use bigquery to get the compute result same as nvarchar in SQL server, how to do this?

1 Like
2

Hi, unfortunately there is no way to do that, NVARCHAR in SQL Server is UTF-16 (NonUnicode) and VARCHAR is UTF-8 (Unicode), that’s why if you do something like:

DECLARE @hash NVARCHAR(MAX) = 'HELLO WORLD'
SELECT HASHBYTES('SHA1', 'HELLO WORLD') AS SIMPLE_INPUT,
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

You will receive different values (See the image bellow)

jbocachica_0-1666129403229.png
jbocachica_0-1666129403229.png1353×397 32 KB

But if you just change the way to interpret the entry variable, you will do a cast on the fly.

DECLARE @hash NVARCHAR(MAX) = 'HELLO WORLD'
SELECT HASHBYTES('SHA1', N'HELLO WORLD') AS SIMPLE_INPUT,
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

You will receive the same values (See the N at the begining)

jbocachica_2-1666129570009.png
jbocachica_2-1666129570009.png1392×387 30.3 KB

In BigQuery, the STRING data type is a UTF-8 (https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#data_type_sizes), so, the result will be always as a SQL SERVER VARCHAR.

Regards from Colombia

John