title: How to convert hex string to binary in SQL Server 2005?
body: <p>How can i convert a hex string in SQL Server to binary?</p>
  
  <p>Better yet, how can i convert a hex string in SQL Server to an integer?</p>
  
  <p>The problem is that every existing answer on Stackoverflow assumes SQL Server 2008.</p>
  
  <h1>Failed attempts</h1>
  
  <ul>
  <li><p><strong><a href="https://stackoverflow.com/questions/39674205/convert-hex-string-to-binary-sql-server">Convert hex string to binary SQL Server</a></strong></p>
  
  <pre><code>SELECT CONVERT(binary(16),'0x01',1)
  0x30783031000000000000000000000000
  </code></pre></li>
  <li><p><strong><a href="https://stackoverflow.com/questions/14145904/sql-server-hex-string-to-varbinary-conversion">SQL Server hex string to varbinary conversion</a></strong></p>
  
  <pre><code>select CONVERT(varbinary(max), '0x01', 1);
  0x30783031
  
  select CONVERT(varbinary(max), '01', 2);
  0x3031
  </code></pre></li>
  <li><p><strong><a href="https://stackoverflow.com/questions/703019/convert-integer-to-hex-and-hex-to-integer">Convert integer to hex and hex to integer</a></strong></p>
  
  <pre><code>-- If the '0x' marker is present:
  SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000001', 1))
  808464433
  
  -- If the '0x' marker is NOT present:
  SELECT CONVERT(INT, CONVERT(VARBINARY, '000001', 2))
  808464433
  </code></pre></li>
  </ul>
  
  <h2>Edit</h2>
  
  <p>...yes 2005 has varbinary. Even 2000 has <code>varbinary</code>:</p>
  
  <pre><code>SELECT name, xtype FROM systypes WHERE name LIKE '%binary%'; 
  SELECT @@version;
  
  name       xtype
  ---------  -----
  varbinary  165
  binary     173
  
  (No column name)
  ---------------------------------
  Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
  May  3 2005 23:18:38 
  Copyright (c) 1988-2003 Microsoft Corporation
  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
  </code></pre>
  
  <p><a href="https://www.databasejournal.com/features/mssql/article.php/1442371/SQL-Server-65-Datatypes.htm" rel="nofollow noreferrer">Even SQL Server 6.5 has varbinary.</a> *(<a href="http://archive.is/C9iPK" rel="nofollow noreferrer">archive</a>)*Interesting, and typical SO fashion, to try to circumvent the question rather than answer it.</p>