SUBSTRING() and hex value

Question

How does substring work on hexadecimal values?

See this:

PRINT SUBSTRING(0x6,1,1)
PRINT SUBSTRING(0xF6,1,1)
PRINT SUBSTRING(0xFF6,1,1)
PRINT SUBSTRING(0xFFF6,1,1)

...outputs...

0x06
0xF6
0x0F
0xFF

...which currently looks as a complete nonsense to me. But it can somehow used in detecting which column is updated in trigger (see COLUMNS_UPDATED()).

I tried:

• intermediately converted it to string and then substring it
• intermediately converted it to int, then string and then substring it
• searching in MSDN substring() documentation
• googling

Can someone explain how is that conversion done?

Answer 1

The SUBSTRING documentation isn't particularly clear about how it treats binary values, referring to 'characters' rather than bytes. But it's effectively treating the input as a byte sequence, and with 1, 1 you are asking for the first byte of the sequence:

Input    Byte sequence
----------------------
0x6      06
0xF6     F6
0xFF6    0F F6
0xFFF6   FF F6

Output of SUBSTRING(input, 1, 1) :

0x06
0xF6
0x0F
0xFF