title: Decimal/Smallint to Hexadecimal conversion question
body: <p>My question has to do with some of the details involved with decimal and hexadecimal conversion as part of a SQL query.</p>
  
  <p>Below is a sample of some of original code:</p>
  
  <pre><code>SELECT
      a.COMMUNICATION_PROTOCOL + '_' + CAST(a.ABC_ID1 AS VARCHAR) 
      + '_' + CAST(a.CODE_CNT1 AS VARCHAR) + '_' + CAST(a.ABC_ID2 AS VARCHAR)
      + '_' + CAST(a.CODE_CNT2 AS VARCHAR) + '_' 
      + CASE 
          WHEN a.TEST_1 = 'Completed' THEN 'Y'
          WHEN a.TEST_1 = 'NotCompleted' THEN 'N'
      END 
      + '_'
      + CASE 
          WHEN a.TEST_2 = 'Completed' THEN 'Y'
          WHEN a.TEST_2 = 'NotCompleted' THEN 'N'
      END
  FROM
      dbo.All_Tests AS a
  </code></pre>
  
  <p>The code is designed to return a single letter communication protocol identifier, and two-character small-integer identifier, along with whether the tests are supported or not supported. Underscores are inserted between each. So, an example result would be A_14_10_17_40_Y_N.</p>
  
  <p>The problem is that ABC_ID1 and ABC_ID2 are actually hexadecimals, while the others are varchar or small integers. So, in the above example, A_14_10_17_40_Y_N should really read A_E_10_11_40_Y_N, because Decimal (14) = Hexadecimal (0x00000E), and Decimal (17) = Hexadecimal (0x000011). The output code only needs the last two characters in the hexadecimal sequence.</p>
  
  <pre><code>SELECT
      ,CONVERT(VARBINARY(8), a.ABC_ID1)
  FROM [...]
  </code></pre>
  
  <p>In the test above, The small-integer decimal ABC_ID1 is converted and put out as 0x000E for Decimal (14), and 0x0011 for Decimal (17). I'm not sure if I'm thinking about this the right way, though. IA LEN operator wouldn't help, since the hexadecimal in this case can be either one character (E) or two characters (11), so I'm not sure what the best approach is. Secondly, given the needed decimal to hexadecimal conversion, is it more appropriate to use CONVERT to insert the two-character hexadecimals into the code's character string, or can CAST be used?</p>
  
  <p>Any thoughts or guidance would be greatly appreciated.</p>