I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-SQL that will convert the string '16A1', or a modified version of it, into the hex number 0x000016A1?

2 Answers 2


I'm not aware of a function to specifically do that but it's easy enough to accomplish with the following:

  '0x' + RIGHT('00000000' + '16A1', 8) AS SomeHexString

Example dbfiddle.uk

As in line with the other suggestions you could also cast the value to hex with the BINARY data type like so:

            CONCAT('00000000', '16A1'),
    ) AS SomeHexField

This should account for varying length values in your quasi-hex string.

Example dbfiddle.uk

If the input hex string can be nullable, make sure to un-null it with ISNULL(), COALESCE() or switching the + operator inside the RIGHT() function to a CONCAT() function (like my second option) instead.

  • 3
    Why not actually make it a hex value? SELECT b4 = CONVERT(binary(4), CONCAT('0000', '16A1'), 2); Mar 24 at 13:42
  • Thank you both. The answer and the comment gave me exactly what I needed.
    – Tim
    Mar 24 at 17:15

You can just convert it to binary(2) with style 2, and then concat it with 0x0000 (the binary value, not a string)

SELECT 0x0000 + CONVERT(binary(2), '16A1', 2);


  • 1
    Does this work if the input value changes length?
    – J.D.
    Mar 24 at 16:31
  • No, but it doesn't hugely make sense anyway then, because hex values are little-endian ie the first byte should be on the right not the left. Mar 24 at 17:28
  • Meant to say left not right Mar 25 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.