0

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

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.

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

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

3
  • 1
    Does this work if the input value changes length?
    – J.D.
    Commented Mar 24, 2023 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. Commented Mar 24, 2023 at 17:28
  • Meant to say left not right Commented Mar 25, 2023 at 20:20

Your Answer

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

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