Convert HashBytes to VarChar

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.

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.

SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'Hello World'),2)

I have found the solution else where:

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)