title: T-SQL Get bytes out of VARCHAR without converting to UNICODE
body: <p>We store a password hash in the database. Basically we get the MD5 hash (array of bytes) and then run Encoding.Unicode.GetChars on the byte array and store that resulting string in the database.  Then when someone logs in we hash their password the same way and compare the two strings.</p>
  
  <p>This works great, except for that I cannot extract a password hash in T-SQL and insert it into another row.  The only way I can get that to work is if I actually set the password hash directly from a select of another row with a valid hash.</p>
  
  <p>I have tried to convert the string to hex and let SQL convert it, but that does not work either.  I assume it's because the bytes we encode as UNICODE are probably don't make up a valid UNICODE string.</p>
  
  <p>So for instance (<a href="http://timscyclingblog.wordpress.com/2012/01/12/sql-server-convert-a-string-into-a-hex-string-and-back-sql-sqlserver/" rel="noreferrer">SQL Server: Convert a string into a hex string and back</a>):</p>
  
  <p>When you run the following command, the word 'Help' gets converted to var binary and then back to a string and hey presto!  You end up with 'Help' again.</p>
  
  <pre><code>SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), 'Help'))
  </code></pre>
  
  <p>Problem is that when I run that on my password hash column, the resulting value is not the same as is stored in the column.  If I paste the string containing the hash into SQL Management studio between two ' symbols, it jumps past the ' and puts in some other characters.  Again, probalby trying to interperet an incomplete UNICODE sequence seeing as it's not a proper UNICODE string.</p>
  
  <p>So what I am looking for is a way to simply take the bytes in the VARCHAR(64) that contains the password hash and write it to an encode text format, something like:</p>
  
  <pre><code>0x0F037584C99E7FD4F4F8C59550F8F507
  </code></pre>
  
  <p>So that I can then do something like</p>
  
  <pre><code>UPDATE   [User]
  SET      PasswordHash = CONVERT(NVARCHAR(64), 0x0F037584C99E7FD4F4F8C59550F8F507)
  WHERE    UserID = 123
  </code></pre>
  
  <p>Any ideas?</p>