Convert HashBytes to VarChar

Question

I want to get the MD5 Hash of a string value in SQL Server 2005  I do this with the following command   SELECT HashBytes  MD5    HelloWorld     However  this returns a VarBinary instead of a VarChar value  If I attempt to convert 0x68E109F0F40CA72A15E05CC22786F8E6 into a VarChar I get h                        instead of 68E109F0F40CA72A15E05CC22786F8E6   Is there any SQL-based solution   Yes

User · Answer

SELECT CONVERT NVARCHAR 32  HashBytes  MD5    Hello World   2

User · Answer

I have found the solution else where   SELECT SUBSTRING master dbo fn varbintohexstr HashBytes  MD5    HelloWorld     3  32

User · Answer

Use master dbo fn varbintohexsubstring 0  HashBytes  SHA1    input   1  0  instead of master dbo fn varbintohexstr and then substringing the result   In fact fn varbintohexstr calls fn varbintohexsubstring internally  The first argument of fn varbintohexsubstring tells it to add 0xF as the prefix or not  fn varbintohexstr calls fn varbintohexsubstring with 1 as the first argument internaly   Because you don t need 0xF  call fn varbintohexsubstring directly

User · Answer

Changing the datatype to varbinary seems to work the best for me

User · Answer

convert varchar 34   HASHBYTES  MD5   Hello World   1     1 for converting hexadecimal to string   convert this to lower and remove 0x from the start of the string by substring   substring lower convert varchar 34   HASHBYTES  MD5   Hello World   1   3 32    exactly the same as what we get in C  after converting bytes to string

User · Answer

Contrary to what David Knight says  these two alternatives return the same response in MS SQL 2008   SELECT CONVERT VARCHAR 32  HashBytes  MD5    Hello World   2  SELECT UPPER master dbo fn varbintohexsubstring 0  HashBytes  MD5    Hello World    1  0     So it looks like the first one is a better choice  starting from version 2008

User · Answer

With personal experience of using the following code within a Stored Procedure which Hashed a SP Variable I can confirm  although undocumented  this combination works 100  as per my example    var SUBSTRING master dbo fn varbintohexstr HashBytes  SHA2 512    SPvar    3  128

[sql] Convert HashBytes to VarChar

Examples related to sql

Examples related to sql-server