Generate MD5 hash string with T-SQL

Question

Is there a way to generate MD5 Hash string of type varchar(32) without using fn_varbintohexstr

SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'email@dot.com')), 3, 32)

So it could be used inside a view with SCHEMABINDING

See also https://stackoverflow.com/questions/35200452/compute-md5-hash-of-a-utf8-string/35289890#35289890 — Ben, Oct 11 '17 at 20:34

score 241 · Accepted Answer · answered Aug 19 '10 at 20:40

241

CONVERT(VARCHAR(32), HashBytes('MD5', 'email@dot.com'), 2)

answered Aug 19 '10 at 20:40

Konstantin Tarkus

37,618
14
135
121

How do you do this in SQL2005? It's not returning properly encoded string...? – Joshua F. Rountree Jun 14 '12 at 12:16
@JoshuaRountree, see my answer at http://stackoverflow.com/questions/3525997/generate-md5-hash-string-with-t-sql/28903685#28903685 – deldev Mar 06 '15 at 16:55

score 79 · Answer 2 · answered Aug 19 '10 at 20:41

79

Use HashBytes

SELECT HashBytes('MD5', 'email@dot.com')

That will give you 0xF53BD08920E5D25809DF2563EF9C52B6

-

SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'email@dot.com'),2)

That will give you F53BD08920E5D25809DF2563EF9C52B6

answered Aug 19 '10 at 20:41

SQLMenace

132,095
25
206
225

1

@Brendan, you left off the ",2)" at the end. – Ryan Elkins Jun 24 '13 at 15:32
1

@RyanElkins I get the same result as Brendan, and I am certainly included the ",2)" :( – Matthew Jul 08 '13 at 21:02

score 20 · Answer 3 · edited Oct 12 '12 at 11:16

20

Solution:

SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5','your text')),3,32)

edited Oct 12 '12 at 11:16

j0k

22,600
28
79
90

answered Oct 12 '12 at 11:02

Dellas

201
2
2

score 19 · Answer 4 · answered May 15 '14 at 14:28

19

None of the other answers worked for me. Note that SQL Server will give different results if you pass in a hard-coded string versus feed it from a column in your result set. Below is the magic that worked for me to give a perfect match between SQL Server and MySql

select LOWER(CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar, EmailAddress)), 2)) from ...

answered May 15 '14 at 14:28

jmacinnes

1,589
1
11
21

1

Using `LOWER()` is only necessary if it is case sensitive. – T.Coutlakis May 17 '14 at 19:42
The first conversion turns out to be important. This gives identical `MD5` Hash compared to `MD5()` function of `Postgresql`. I was wonder why the `MD5` hashs differs from `Python` and `Postgresql`. Thanks for the recipe.. – Ben Mar 24 '18 at 13:57

slartidan · Answer 5 · 2018-09-26T08:32:19.977

18

For data up to 8000 characters use:

CONVERT(VARCHAR(32), HashBytes('MD5', 'email@dot.com'), 2)

Demo

For binary data (without the limit of 8000 bytes) use:

CONVERT(VARCHAR(32), master.sys.fn_repl_hash_binary(@binary_data), 2)

Demo

edited Sep 26 '18 at 08:32

answered Aug 05 '16 at 13:10

slartidan

20,403
15
83
131

score 5 · Answer 6 · edited May 23 '19 at 14:12

5

try this:

select SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  'email@dot.com' )),3,32)

edited May 23 '19 at 14:12

slartidan

20,403
15
83
131

answered Mar 06 '15 at 16:54

deldev

1,296
18
27

score 1 · Answer 7 · edited Dec 03 '19 at 22:07

1

SELECT CONVERT(
      VARCHAR(32),
      HASHBYTES(
                   'MD5',
                   CAST(prescrip.IsExpressExamRX AS VARCHAR(250))
                   + CAST(prescrip.[Description] AS VARCHAR(250))
               ),
      2
  ) MD5_Value;

works for me.

edited Dec 03 '19 at 22:07

Dharman

30,962
25
85
135

answered Dec 03 '19 at 22:05

Gita

69
1

score 1 · Answer 8 · answered Feb 01 '20 at 22:46

1

You didn't explicitly say you wanted the string to be hex; if you are open to the more space efficient base 64 string encoding, and you are using SQL Server 2016 or later, here's an alternative:

select SubString(h, 1, 32) from OpenJson(
    (select HashBytes('MD5', 'email@dot.com') h for json path)
) with (h nvarchar(max));

This produces:

9TvQiSDl0lgJ3yVj75xStg==

answered Feb 01 '20 at 22:46

N8allan

2,138
19
32

doesn't work "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." – symbiont Oct 31 '22 at 13:58
@symbiont you have to be in a database with a newer support level; it won't work in an older version or in Master. – N8allan Nov 02 '22 at 14:22

score 1 · Answer 9 · answered Sep 06 '22 at 17:01

Again, most solutions doesn´t work properly, this is carefully tested to return unique results over a cobination of 10 different text columns (KEY CHANGE: convert to varchar(X) where x is the maximum lenght of your string < 4000, for longer strings, use other methods):

LOWER(CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar(4000), EmailAddress)), 2))

score 0 · Answer 10 · edited Aug 21 '19 at 04:36

declare @hash nvarchar(50)
--declare @hash varchar(50)

set @hash = '1111111-2;20190110143334;001'  -- result a5cd84bfc56e245bbf81210f05b7f65f
declare @value varbinary(max);
set @value = convert(varbinary(max),@hash);


select  
 SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', '1111111-2;20190110143334;001')),3,32) as 'OK'
,SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @hash)),3,32) as 'ERROR_01'
,SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',convert(varbinary(max),@hash))),3,32) as 'ERROR_02'
,SUBSTRING(sys.fn_sqlvarbasetostr(sys.fn_repl_hash_binary(convert(varbinary(max),@hash))),3,32)
,SUBSTRING(sys.fn_sqlvarbasetostr(master.sys.fn_repl_hash_binary(@value)),3,32)

Solution ... declare @hash varchar(50) https://md5.gromweb.com/?string=1111111-2%3B20190110143334%3B001 — fernando yevenes, Feb 20 '19 at 15:50

Generate MD5 hash string with T-SQL

10 Answers10

Linked