<p>I want to get the MD5 Hash of a string value in SQL Server 2005. I do this with the following command:</p>

<pre><code>SELECT HashBytes('MD5', 'HelloWorld')
</code></pre>

<p>However, this returns a VarBinary instead of a VarChar value. If I attempt to convert <code>0x68E109F0F40CA72A15E05CC22786F8E6</code> into a VarChar I get <code>há ðô§*à\Â'†øæ</code> instead of <code>68E109F0F40CA72A15E05CC22786F8E6</code>.</p>

<p>Is there any SQL-based solution?</p>

<p><strong><a href="https://stackoverflow.com/questions/2120/#2382">Yes</a></strong></p>

[{'answer_id': 2382, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 8, 'selected': True, 'text': "<p>I have found the solution else where:</p>\n\n<pre><code>SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)\n</code></pre>\n"}, {'answer_id': 2284546, 'author': 'anopres', 'author_id': 1537, 'author_profile': 'https://Stackoverflow.com/users/1537', 'pm_score': -1, 'selected': False, 'text': '<p>Changing the datatype to varbinary seems to work the best for me.</p>\n'}, {'answer_id': 4585246, 'author': 'Rapscallion', 'author_id': 561339, 'author_profile': 'https://Stackoverflow.com/users/561339', 'pm_score': 7, 'selected': False, 'text': "<pre><code>SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'Hello World'),2)\n</code></pre>\n\n\n"}, {'answer_id': 5341695, 'author': 'Xaqron', 'author_id': 313421, 'author_profile': 'https://Stackoverflow.com/users/313421', 'pm_score': 5, 'selected': False, 'text': "<p>Use <code>master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', @input), 1, 0)</code> instead of <code>master.dbo.fn_varbintohexstr</code> and then <code>substringing</code> the result.</p>\n\n<p>In fact <code>fn_varbintohexstr</code> calls <code>fn_varbintohexsubstring</code> internally. The first argument of <code>fn_varbintohexsubstring</code> tells it to add <code>0xF</code> as the prefix or not. <code>fn_varbintohexstr</code> calls <code>fn_varbintohexsubstring</code> with <code>1</code> as the first argument internaly.</p>\n\n<p>Because you don't need <code>0xF</code>, call <code>fn_varbintohexsubstring</code> directly.</p>\n"}, {'answer_id': 7330431, 'author': 'Ramans', 'author_id': 932224, 'author_profile': 'https://Stackoverflow.com/users/932224', 'pm_score': 4, 'selected': False, 'text': "<pre><code>convert(varchar(34), HASHBYTES('MD5','Hello World'),1)\n</code></pre>\n\n<p>(1 for converting hexadecimal to string)</p>\n\n<p>convert this to lower and remove 0x from the start of the string by substring:</p>\n\n<pre><code>substring(lower(convert(varchar(34), HASHBYTES('MD5','Hello World'),1)),3,32)\n</code></pre>\n\n<p>exactly the same as what we get in C# after converting bytes to string</p>\n"}, {'answer_id': 25541426, 'author': 'Timo Riikonen', 'author_id': 3733733, 'author_profile': 'https://Stackoverflow.com/users/3733733', 'pm_score': 5, 'selected': False, 'text': '<p>Contrary to what <a href="https://stackoverflow.com/users/161332/david-knight">David Knight</a> says, these two alternatives return the same response in MS SQL 2008:</p>\n\n<pre><code>SELECT CONVERT(VARCHAR(32),HashBytes(\'MD5\', \'Hello World\'),2)\nSELECT UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes(\'MD5\', \'Hello World\'), 1, 0))\n</code></pre>\n\n<p>So it looks like the first one is a better choice, starting from version 2008.</p>\n'}, {'answer_id': 28707974, 'author': 'Simon Jones', 'author_id': 4300786, 'author_profile': 'https://Stackoverflow.com/users/4300786', 'pm_score': 1, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>@var=SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA2_512', @SPvar)), 3, 128)\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/2120', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/383/']