Search code examples
sqlhashhanasha

Generate a hash column for table columns on SAP HANA

I have a table TAB1 (ID, NAME, LASTNAME, AGE, DATEOFBIRTH, ID_CARD_NBR, SHA)

I need to generate a hash for these columns in a column SHA.

How to make the code insert/update the SHA column?

Thank you

The query to obtain that hash on SAP HANA:

SELECT hash_sha256(
to_varbinary("ID"),
to_varbinary(IFNULL("NAME",'0')),
to_varbinary(IFNULL("LASTNAME",'0')),
to_varbinary(IFNULL("AGE",'0')),
to_varbinary(IFNULL("DATEOFBIRTH",'0')),
to_varbinary(IFNULL("ID_CARD_NBR",'0')) )

FROM "MYSCHEMA"."PERSONS"
Solution

  • If Id is the primary key(can't be NULL) you can do this : 

    UPDATE PERSONS
SET SHA = hash_sha256(to_varbinary("ID"),to_varbinary(IFNULL("NAME",'0')),to_varbinary(IFNULL("LASTNAME",'0')),to_varbinary(IFNULL("AGE",'0')),to_varbinary(IFNULL("DATEOFBIRTH",'0')),to_varbinary(IFNULL("ID_CARD_NBR",'0')) )
WHERE SHA IS NULL;  -- or other condition