Hey there! Let's break down why your conversion is failing and how to fix it.

问题根源 #

When using CONVERT(VARBINARY, ..., 2) (Style 2), SQL Server expects the input varchar string to meet two strict requirements:

• It must contain only valid hexadecimal characters (0-9, A-F, a-f)
• The length of the string must be even — because each byte in varbinary is represented by two hex characters.

Looking at your examples:

• 'AFE27AF97DC6' is 12 characters (even) → works perfectly, since it maps to 6 bytes of binary data.
• 'AFEE27AF97DC6' is 13 characters (odd) → there's no way to split an odd number of hex chars into whole bytes, so SQL Server throws the Error converting data type varchar to varbinary error.

解决方案 #

Here are a few approaches to fix this:

1. Fix the input string length
   Depending on your business logic, you can either:

   • Trim the extra character if it's a typo (e.g., remove the extra E to get back to an even-length string)
   • Pad the string with a leading or trailing 0 to make its length even. For example:

     -- Pad with leading 0
     SELECT CONVERT(VARBINARY(MAX), '0AFEE27AF97DC6', 2)
     -- Or pad with trailing 0
     SELECT CONVERT(VARBINARY(MAX), 'AFEE27AF97DC60', 2)
     

   Note: Make sure padding aligns with your data's actual meaning — don't just add a 0 randomly!

2. Validate input before conversion
   Use TRY_CONVERT instead of CONVERT to avoid hard errors; it will return NULL for invalid inputs instead of throwing an exception:

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

   You can also add a check for even length upfront:

   SELECT 
     CASE 
       WHEN LEN(your_column) % 2 = 0 THEN CONVERT(VARBINARY(MAX), your_column, 2)
       ELSE NULL -- Or handle the odd-length case as needed
     END
   FROM your_table
   

3. Ensure no invalid characters
   Double-check that your input strings don't contain non-hex characters (like G-Z, symbols, etc.) — those will also trigger the same conversion error.

内容的提问来源于stack exchange，提问作者rav