CODEWITHSUNDEEP
sql-server-2008
pal2ie
pal2ie

Reputation: 83

getting different values of HASHBYTES(MD5) if inserted in table Vs. applied same function in SELECT statement

I have a table TBLxx with columns xxANSI and xxMD5 I am using below to calculate the checksum MD5 of xxANSI

update i
  set [xxMD5] = HASHBYTES ('MD5', [xxANSI])
from TBLxx i

The result I get in the MD5 column is a set of special charaters such as "#8U3š]PÍÛ'›÷‚¤Ä" whereas if I do a select on that column while applying the HASHBYTES function, I get a value "0x233855339A5D50CD0CDB279BF782A4C4"

select top 1 xxANSI, xxMD5, hashbytes('MD5', xxMD5)
from TBLxx

"saco_other_test", "’3è@GÔ¦l
VŒ$t>", "0x169233E8401947D4A66C0D568C24743E"

(there is a line break within the value of column xxMD5 which is the reason for the line break above)

Does anyone know why this is happening? How can I avoid this? I want values such as "0x169233E8401947D4A66C0D568C24743E" in my table column.

Thanks in advance

Upvotes: 2

Views: 1077

Answers (1)

AdamL
AdamL

Reputation: 13161

I think that your problem may be that you're inserting hashbytes results to VARCHAR column. You should use BINARY type to store hashbytes results.

See the difference:

select hashbytes('MD5','saco_other_test') 
select cast(hashbytes('MD5','saco_other_test') as varchar(255))

Upvotes: 1

Related Questions