Search code examples
sql-serversql-server-2017hashbytes

Convert HASBYTES function output to CHAR

I know that the best data type for storing output of the HASHBYTES function is BINARY/VARBINARY, but we want to store it as CHAR as it is suggested by DataVault best practices, moreover not all tolls support keys of BINARY types, for example PowerBI.

So the question is, how to convert it to CHAR? When I do simple CAST/CONVERT I get different output rather than raw output of HASHBYTES function.

SELECT  CONVERT(CHAR(32), HASHBYTES('MD5', 'test'))
SELECT  CAST(HASHBYTES('MD5', 'test') AS CHAR(32))
SELECT HASHBYTES('MD5', 'test')

Expected value is 098F6BCD4621D373CADE4E832627B4F6 (without 0x) and actual value is kÍF!ÓsÊÞNƒ&'´ö

Solution 
  • --convert binary to char without 0x prefix, using style 2
SELECT CONVERT(CHAR(32), HASHBYTES('MD5', 'test'), 2);