Error converting VarChar to VarBinary in SQL Server

Question

I need to convert varchar to varbinary. The query I'm using is working correctly for only some values.

This one is working fine

SELECT CONVERT (VARBINARY(MAX), 'AFE27AF97DC6', 2)

while this one throws an error

Error converting data type varchar to varbinary

SELECT CONVERT (VARBINARY(MAX), 'AFEE27AF97DC6', 2)

I need to use style 2.

I've read all the similar questions but I couldn't find the solution. Any thought would help me. Thank you!

Answer 1

Hmm, AFEE27AF97DC6 is one nibble short and it seems like only full bytes are accepted. Try to zero pad it. E.g.

SELECT convert(varbinary(max), '0AFEE27AF97DC6', 2)

You can also wrap it in a CASE expression checking if the string has an even or odd length, should the strings be variable.

SELECT convert(varbinary(max),
               CASE
                 WHEN len('AFEE27AF97DC6') % 2 <> 0 THEN
                   concat('0', 'AFEE27AF97DC6')
                 ELSE
                   'AFEE27AF97DC6'
               END,
               2)

(Replace the literals with your variable.)

Answer 2

This is pretty clearly stated in the documentation:

1, 2 [for the third argument]

For a binary data_type, the expression must be a character expression. The expression must have an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f).

Your first string has a length of 12, so it converts fine. The second has a length of 13, so it is not valid.

I am not sure what you intend, but a 0 in the 3rd position gives similar results for the two conversions:

SELECT CONVERT(varbinary(max),'AFE27AF97DC6' , 2), CONVERT(varbinary(max),'AF0EE27AF97DC6' , 2) 

Gives:

0xAFE27AF97DC6  0xAF0EE27AF97DC6