Convert hex string representation to hex number in SQL Server T-SQL

Question

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?

Answer 1

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

SELECT 
  '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:

SELECT
    CONVERT
    (
        BINARY(4),
        RIGHT
        (
            CONCAT('00000000', '16A1'),
            8
        ),
        2
    ) 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.

Answer 2

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);

db<>fiddle