Working with MD5 on SQL Server

There are times you might need to convert some information to MD5 on your SQL Server in HEX format. As it is for now there is no out of the box function to do it on SQL Server. But you can easily accomplish this task by using the following function:

create FUNCTION [dbo].[md5]
(
        @value varchar(4000)
)
RETURNS varchar(40)
AS
BEGIN
  return
    case when @value is not null then
      (SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', @value)), 3, 32))
    else null end
END
GO

Testing it is quite straight forward. For example:

select dbo.md5('123') 

And here is the output:

202cb962ac59075b964b07152d234b70