title: MS SQL Server comparing values with HEX
body: <p>Must have overlooked something here with this query that converts and compares vales in hex</p>
  
  <pre><code>select NAME, convert(varchar, convert(Varbinary(MAX), NAME),2), convert(varchar, convert(Varbinary(MAX), 'freddy'),2) from CUSTOMERS 
  </code></pre>
  
  <p>works fine, but as you can see, value retrieved from database is padded with 00 blanks</p>
  
  <pre><code>freddy  660072006500640064007900    667265646479
  </code></pre>
  
  <p>wasn't expecting that... is there any way of dropping them..?</p>
  
  <p>Oh, additional information, the 00 is provision for unicode characters, which is good, however I just want my hexed "freddy" to match my retrieved "freddy" in hex</p>
  
  <p>Any guidance gratefully accepted, cheers</p>
  
  <p>Sorted next day by adding an extra <strong>convert(varchar(MAX), USRPWRD)</strong> so this;</p>
  
  <pre><code>convert(varchar, convert(Varbinary(MAX), convert(varchar(MAX), USRPWRD)),2)
  </code></pre>
  
  <p>returns this</p>
  
  <pre><code>667265646479
  </code></pre>
  
  <p>wouldn't be so good if unicode characters were required in the field</p>
  
  <p>ANOTHER DAY OR TWO LATER...</p>
  
  <p>Now I've discovered that my SQL query only returns part of the hex. Here is the value of the email address in my database;</p>
  
  <pre><code>whatever.happened@rumplestiltzken.com
  </code></pre>
  
  <p>This is the email address converted to hex</p>
  
  <pre><code>77686174657665722E68617070656E65644072756D706C657374696C747A6B656E2E636F6D
  </code></pre>
  
  <p>This is the relevant part of my query</p>
  
  <pre><code>convert(varchar, convert(Varbinary(MAX), convert(varchar(MAX), USREMAIL)),2)
  </code></pre>
  
  <p>This is what it returns, which is exactly the first 30 chars of the full hex string above</p>
  
  <pre><code>77686174657665722E68617070656E
  </code></pre>
  
  <p>How do I get the full hex returned..? Help please!</p>
  
  <p>SOLVED (needs (MAX) on the final vchar)</p>
  
  <pre><code>convert(varchar(MAX), convert(Varbinary(MAX), convert(varchar(MAX), USREMAIL)),2)
  </code></pre>