I have a query in SAP HANA like below. I am calculating check_sum based on some column in the table

select Name, address, salary, phone_number, department, HASH_SHA256( TO_BINARY(COALESCE(Name, 'ABC')), TO_BINARY(COALESCE(address, 'ABC')), TO_BINARY(COALESCE(salary, 0)), TO_BINARY(COALESCE(department, 'ABC'))) as check_sum from table1;

Now I have created the same table in Big Query.

I want to calculate check_sum in Big Query and get the same result as in SAP HANA

I have tried like below

select Name, address, salary, phone_number, department, TO_HEX(SHA256((TO_BINARY(COALESCE(Name, 'ABC')), TO_BINARY(COALESCE(address, 'ABC')), TO_BINARY(COALESCE(salary, 0)), TO_BINARY(COALESCE(department, 'ABC')))) as check_sum from table1;

It says TO_BINARY function is not available.

I tried to convert the column to BYTES but it says NUMERIC cannot be converted to BYTES

select Name, address, salary, phone_number, department, TO_HEX(SHA256(CAST(COALESCE(Name, 'ABC') AS BYTES), CAST(COALESCE(address, 'ABC') AS BYTES), CAST(COALESCE(salary, 0) AS BYTES), CAST(COALESCE(department, 'ABC') AS BYTES))) as check_sum from table1;

But it is still not working. What is the correct way to match the result.

CodePudding user response：

Try like below this should give you correct values

select Name, address, salary, phone_number, department, UPPER(TO_HEX(SHA256(CONCAT(CAST(COALESCE(Name, 'ABC') AS STRING), CAST(COALESCE(address, 'ABC') AS STRING), CAST(COALESCE(salary, 0) AS STRING), CAST(COALESCE(department, 'ABC') AS STRING))))) as check_sum from table1;