Create hashvalue of all column except primary key

I have a query for MySQL to get hashvalue of table columns except for the primary key.

I need to do same thing for SQL Server so far I have tried hashbytes but the result is not looking right.

The query for MySQL I'm using to create hash values for all columns except primary key is this


SELECT MD5(CONCAT(ISNULL(COLUMN1,''),ISNULL(COLUMN2,''),ISNULL(COLUMN3,''))) as HASHVALUE, PRIMARYKEY FROM TABLE_NAME1

And what I have tried with SQL so far is this..

SELECT HashBytes('MD5', COLUMN1) AS HASHVALUE , PRIMARYKEY FROM TABLE_NAME1

Result from mysql >

|HASHVALUE                        | Primary |   
|:------------------------------- |:-------:|                   
|7a16284f87ab262f0047f4c0b4e50b2c | 0       |
|d41b398c603086da409e87ee35824cf6 | 2       |
|c0e7b9b9c29703282a9f192a4c0aead9 | 6       |
|ce13595c356a0a373140f3fda1eb5fb3 | 7       |

Result from SQL >

| HASHVALUE     | PRIMARY |
| ----------    | ------- |
|ã $t  J ¦OlU   | 1       |

CodePudding user response：

The SQL Server result is still in binary. You must convert to a string:

LOWER(CONVERT(varchar(32), HashBytes('MD5', COLUMN1), 2))

See it here to prove you get the same results, even after combining columns:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b11c422fe9236043ba5edbeb7d97043d

You can use the menu at the top of the linked page to switch between MySql and SQL Server.