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

User · Answer

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

User · Answer

try this   select SUBSTRING sys fn sqlvarbasetostr HASHBYTES  MD5     email dot com     3 32

User · Answer

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

User · Answer

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

User · Answer

SELECT CONVERT        VARCHAR 32         HASHBYTES                      MD5                      CAST prescrip IsExpressExamRX AS VARCHAR 250                        CAST prescrip  Description  AS VARCHAR 250                           2     MD5 Value    works for me

User · Answer

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

User · Answer

CONVERT VARCHAR 32   HashBytes  MD5    email dot com    2

User · Answer

Solution   SUBSTRING sys fn sqlvarbasetostr HASHBYTES  MD5   your text    3 32

User · Answer

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

[sql-server] Generate MD5 hash string with T-SQL

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008