Calculating checksum in Big Query

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.

Answer

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;