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
