MS SQL: MD5 Hash

The non-reversable hashing algorithm MD5 is supported in Microsoft SQL Server, but is not directly accessable though a simple transact-SQL function. Normally you would store the MD5 hash value as a hexadecimal string in your database. The HashBytes function returns a binary array of hash data. To convert the binary data to hex we need to use the function fn_varbintohexstr.


To make your code easier to read, I've made a wrapper in a custom userdefined function:

CREATE FUNCTION [dbo].[sudf_Common_Md5Hash]

(

      -- Add the parameters for the function here

      @strValue nvarchar(max)

)

RETURNS nvarchar(32)

AS

BEGIN

      -- Declare the return variable here

      declare @strResult nvarchar(32)

      -- Generate the MD5

      set @strResult = SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @strValue)), 3, 32)

     

      -- Return the result of the function

      RETURN @strResult

END