blocks|key|950984|text|CONVERT(VARCHAR(32),+HashBytes('MD5',+'email@dot.com'),+2)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|950985|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CONVERT(VARCHAR(32), HashBytes('MD5', 'email@dot.com'), 2)
</code></pre>

blocks|key|951037|text|使用HashBytes|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|951038|SELECT+HashBytes('MD5',+'email@dot.com')|code-block|syntax|javascript|951039|这将得到0xF53BD08920E5D25809DF2563EF9C52B6|951040|-|951041|SELECT+CONVERT(NVARCHAR(32),HashBytes('MD5',+'email@dot.com'),2)|951042|这将为您提供F53BD08920E5D25809DF2563EF9C52B6|951043|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms174415.aspx^0|2|9|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|C|$]]|$1|M|3|N|5|F|7|15|8|@]|9|@]|C|$G|H]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

Use <a href="http://msdn.microsoft.com/en-us/library/ms174415.aspx" rel="noreferrer">HashBytes</a> 

<pre><code>SELECT HashBytes('MD5', 'email@dot.com')
</code></pre>

That will give you 0xF53BD08920E5D25809DF2563EF9C52B6

-

<pre><code>SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'email@dot.com'),2)
</code></pre>

That will give you F53BD08920E5D25809DF2563EF9C52B6

blocks|key|1165075|text|解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1165076|SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5','your+text')),3,32)|code-block|syntax|javascript|1165077|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Solution:

<pre><code>SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5','your text')),3,32)
</code></pre>

blocks|key|1165128|text|其他的答案对我都不起作用。请注意，如果传入的是硬编码字符串，而不是从结果集中的列馈送该字符串，则SQL+Server将给出不同的结果。下面是让我在SQL+Server和MySql之间实现完美匹配的魔术|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1165129|select+LOWER(CONVERT(VARCHAR(32),+HashBytes('MD5',+CONVERT(varchar,+EmailAddress)),+2))+from+...|code-block|syntax|javascript|1165130|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>select LOWER(CONVERT(VARCHAR(32), HashBytes('MD5', CONVERT(varchar, EmailAddress)), 2)) from ...
</code></pre>

blocks|key|1165132|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1165133|select+SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',++'email@dot.com'+)),3,32)+|code-block|syntax|javascript|1165134|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try this:

<pre><code>select SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', 'email@dot.com' )),3,32) 
</code></pre>

blocks|key|1165341|text|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)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1165342|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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)
</code></pre>

blocks|key|951288|text|SELECT+CONVERT(
++++++VARCHAR(32),
++++++HASHBYTES(
+++++++++++++++++++'MD5',
+++++++++++++++++++CAST(prescrip.IsExpressExamRX+AS+VARCHAR(250))
+++++++++++++++++++%2B+CAST(prescrip.[Description]+AS+VARCHAR(250))
+++++++++++++++),
++++++2
++)+MD5_Value;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|951289|这对我很有效。|unstyled|951290|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

works for me.

blocks|key|1165447|text|您没有明确表示您希望字符串是十六进制的；如果您愿意使用更节省空间的base64字符串编码，并且您使用的是SQL+Server+2016或更高版本，那么这里有一个替代方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1165448|select+SubString(h,+1,+32)+from+OpenJson(
++++(select+HashBytes('MD5',+'email@dot.com')+h+for+json+path)
)+with+(h+nvarchar(max));|code-block|syntax|javascript|1165449|这会产生以下结果：|1165450|9TvQiSDl0lgJ3yVj75xStg==|1165451|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

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

This produces:

<pre><code>9TvQiSDl0lgJ3yVj75xStg==
</code></pre>

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

<pre><code>SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'email@dot.com')), 3, 32)
</code></pre>

So it could be used inside a view with SCHEMABINDING

Generate MD5 hash string with T-SQL

有没有一种方法可以在不使用fn_varbintohexstr的情况下生成类型为varchar(32)的MD5散列字符串SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'email@dot.com')), 3, 32)因此它可以通过SCHEMABINDING在视图中使用

问用T-SQL生成MD5哈希串
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用T-SQL生成MD5哈希串EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用T-SQL生成MD5哈希串
EN