MS SQL: Create MD5 Hash string in Transact-SQL

Microsoft SQL ServerLeave a Comment on MS SQL: Create MD5 Hash string in Transact-SQL

MS SQL: Create MD5 Hash string in Transact-SQL

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
Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top