I want to get the MD5 Hash of a string value in SQL Server 2005. I do this with the following command:
SELECT HashBytes('MD5', 'HelloWorld')
However, this returns a VarBinary instead of a VarChar value. If I attempt to convert 0x68E109F0F40CA72A15E05CC22786F8E6
into a VarChar I get há ðô§*à\Â'†øæ
instead of 68E109F0F40CA72A15E05CC22786F8E6
.
Is there any SQL-based solution?
This question is related to
sql
sql-server
I have found the solution else where:
SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)
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.
convert(varchar(34), HASHBYTES('MD5','Hello World'),1)
(1 for converting hexadecimal to string)
convert this to lower and remove 0x from the start of the string by substring:
substring(lower(convert(varchar(34), HASHBYTES('MD5','Hello World'),1)),3,32)
exactly the same as what we get in C# after converting bytes to string
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.
Changing the datatype to varbinary seems to work the best for me.
SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'Hello World'),2)
With personal experience of using the following code within a Stored Procedure which Hashed a SP Variable I can confirm, although undocumented, this combination works 100% as per my example:
@var=SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA2_512', @SPvar)), 3, 128)
Source: Stackoverflow.com