Convert HashBytes to VarChar

Question

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?

Yes

score 163 · Accepted Answer · answered Aug 05 '08 at 14:26

163

I have found the solution else where:

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

answered Aug 05 '08 at 14:26

GateKiller

74,180
73
171
204

25

fn_varbintohexstr is not documented function. Use CONVERT(Char,@value,2) – Cheburek Nov 02 '11 at 11:55
I just got bit by varbinary as needing a way for update into the warehouse. This worked like a charm! thanks... – nitefrog Dec 13 '11 at 22:33
This method is very slow, uses an undocumented function and doesn't work in Azure. Not cool. Use Convert instead! – Rocklan Feb 20 '12 at 05:46
4

CONVERT() doesn't work in SQL 2005. If you're using SQL 2008 or above, then use CONVERT() all you want. Sadly I'm not aware of a single command which will work for all SQL versions, so either do some crazy version checking in your script, or just make a note somewhere that you need to fix the function if you upgrade SQL versions. – Carl Bussema Apr 26 '12 at 18:27
8

CONVERT(Char,@value,2) only outputs 32 bytes - if you do this to a sha1 hash you will truncate it, you need convert(char(48),@value,2) to keep the appropriate output. – Andrew Hill Feb 26 '15 at 02:18
Ahh, good old Microsoft SQL Server... such a clean/obvious/succinct function. ;) .. Compare that to the php equivalent: md5() – Manachi May 17 '16 at 08:15
@AndrewHill it's actually 40 characters. sha1 is 160bit long and the characters use 4bit each, so 160/4 gives you 40 characters. – isapir Jun 23 '17 at 19:50
The problem with `master.dbo.fn_varbintohexstr` is that you can not use it with SCHEMABINDING – isapir Jun 23 '17 at 19:50
I think the question should be modified to have SQL server 2005 in both title and tag as this is a version specific solution. For newer SQL servers the answer by Rapscallion/Jason Plank is more effective as many have noted. – Hansang Jan 15 '19 at 04:37

score 113 · Answer 2 · edited Sep 07 '11 at 16:01

113

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

edited Sep 07 '11 at 16:01

Jason Plank

2,336
5
31
40

answered Jan 03 '11 at 14:26

Rapscallion

1,259
1
8
6

4

this works in SQL Azure. for SHA1 : SELECT CONVERT(VARCHAR(40), HashBytes('SHA1', 'Hello World'), 2) – Raptor Jan 11 '11 at 02:35
4

No need to use nvarchar unnecessarily. – Ian Kemp Mar 01 '12 at 14:21
3

The question states SQL Server 2005 and if you do either of the suggestions above in it (and probably any other version as well) they do not do what is asked for. You get whatever character the bytes are equivalent to, not the bytes as a hex string which is what is asked for. GateKiller and Xarqron give answers that work. – David Knight Apr 04 '12 at 09:48
1

Where can I read about these convert styles? **2** in this case which is passed as the parameter. And how to make an equivalent of that in C# code? What encoding should I choose? – Dmytro Zhluktenko Oct 19 '17 at 16:19

score 32 · Answer 3 · answered Mar 17 '11 at 16:00

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.

score 30 · Answer 4 · edited May 23 '17 at 11:55

30

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.

edited May 23 '17 at 11:55

Community

1
1

answered Aug 28 '14 at 05:35

Timo Riikonen

477
5
8

Do not type this by mistake which gives a subtly different answer!... convert(varchar, HashBytes('MD5', 'Hello World')), 2) – andrew pate Apr 10 '18 at 15:21
correct, I had missed the ",2 " from the convert which is the important part – David Knight Nov 25 '20 at 10:45

score 16 · Answer 5 · edited Sep 07 '11 at 16:00

16

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

edited Sep 07 '11 at 16:00

Jason Plank

2,336
5
31
40

answered Sep 07 '11 at 07:43

Ramans

161
1
2

score 1 · Answer 6 · answered Feb 24 '15 at 22:54

1

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)

answered Feb 24 '15 at 22:54

Simon Jones

47
3

Duplicate of the accepted answer + SHA2_512 hashing algorithm produces 256 characters so you loose the second part of the hash with the SUBSTRING – janv8000 Dec 15 '21 at 15:21

score -3 · Answer 7 · answered Feb 17 '10 at 21:31

-3

Changing the datatype to varbinary seems to work the best for me.

answered Feb 17 '10 at 21:31

mrankin

2,373
3
25
29

Convert HashBytes to VarChar

7 Answers7

Linked

Related