BigQuery compatibility with Netezza hash function for values with special character ¶

[vigneswar17]

Hi Team,

I'm trying to convert NZ functions to BQ compatibility and Im facing some discrepancy when dealing with  hash function for input values with special character.

Below are the SQL statements and corresponding output,

Netezza:

select rawtohex(hash( 'CHR' || 'BN8193877¶' , 1 ))
313E6401C7D3A6A11A0AA54AC251345345D437BB

BigQuery:

select UPPER(TO_HEX(SHA1(CONCAT('CHR', CAST('BN8193877¶' AS STRING)))))
E58916ABC36F6E9D677A49C8C4D8FD523C6E5409

It was working (giving similar output) for input values without special characters but giving different results when handling values with special characters (In this case ¶ ). Both the queries are using SHA1 algorithms.

Any leads on this are much appreciated?