CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
Grief Coder
Grief Coder

Reputation: 6786

Generate MD5 hash string with T-SQL

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

Upvotes: 138

Views: 352654

Answers (10)

Axell Padilla
Axell Padilla

Reputation: 56

Again, most solutions doesn´t work properly, this is carefully tested to return unique results over a combination of 10 different text columns (KEY CHANGE: convert to varchar(X):

CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar(MAX), StringField)), 2) hash_value

Note: Before SQL Server 2016 the string is limited to 7998 varchars (8000 bytes). Source: HASHBYTES MICROSOFT

Upvotes: 2

N8allan
N8allan

Reputation: 2268

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==

Upvotes: 1

Gita
Gita

Reputation: 69

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

works for me.

Upvotes: 1

fernando yevenes
fernando yevenes

Reputation: 152

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)

Upvotes: 0

deldev
deldev

Reputation: 1386

try this:

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

Upvotes: 5

slartidan
slartidan

Reputation: 21608

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

Upvotes: 19

jmacinnes
jmacinnes

Reputation: 1609

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 ...

Upvotes: 20

Dellas
Dellas

Reputation: 201

Solution:

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

Upvotes: 20

SQLMenace
SQLMenace

Reputation: 135171

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

Upvotes: 83

Konstantin Tarkus
Konstantin Tarkus

Reputation: 38428

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

Upvotes: 255

Related Questions