title: MS SQL server - convert HEX string to integer
body: <p>This answer to what looks like the same question:</p>
  <p><a href="https://stackoverflow.com/questions/703019/sql-query-convert-integer-to-hex-and-hex-to-integer">Convert integer to hex and hex to integer</a></p>
  <p>..does not work for me.</p>
  <p>I am not able to go to a HEX string to an integer using MS SQL server 2005 CAST or CONVERT. Am I missing something trivial? I have searched extensively, and the best I can find are long-winded user functions to go from a hex string value to something that looks like a decimal int. Surely there is a simple way to do this directly in a query using built in functions rather than writing a user function?</p>
  <p>Thanks</p>
  <p>Edit to include examples:</p>
  <blockquote>
  <p>select  CONVERT(INT, 0x89)</p>
  </blockquote>
  <p>works as expected, but</p>
  <blockquote>
  <p>select  CONVERT(INT, '0x' + substring(msg, 66, 2)) from sometable</p>
  </blockquote>
  <p>gets me:</p>
  <p>&quot;Conversion failed when converting the varchar value '0x89' to data type int.&quot;</p>
  <p>an extra explicit CAST:</p>
  <blockquote>
  <p>select  CONVERT(INT, CAST('0x89' AS VARBINARY))</p>
  </blockquote>
  <p>executes, but returns 813185081.</p>
  <p>Substituting 'Int', 'Decimal', etc for 'Varbinary' results in an error. In general, strings that appear to be numeric are interpreted as numeric if required, but not in this case, and there does not appear to be a CAST that recognizes HEX. I would like to think there is something simple and obvious and I've just missed it.</p>
  <blockquote>
  <p>Microsoft SQL Server Management Studio Express                    9.00.3042.00</p>
  <p>Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86)   Sep  6 2009 01:43:32   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)</p>
  </blockquote>
  <p>To sum up: I want to take a hex string which is a value in a table, and display it as part of a query result as a decimal integer, using only system defined functions, not a UDF.</p>