SQL Server 2008 convert varchar to varbinary

Question

I try to import image data into a sql server 2008 db with code like this:

INSERT INTO [TAB] (ID_PHOTO,PHOTO)
VALUES(
    CAST('333EFB54-7062-E043-F088-FE0A916C0297' as uniqueidentifier),
    CONVERT(varbinary(max),'0xFFD8FFE000')
)

The string is just a dummy but when I make the insert I found something like this in the database

0x307846464438464645303030

which isn't exactly what I expected. Does anybody know what I have done wrong?

Answer 1

The issue here is simply that a string -- '0xFFD8FFE000' -- is being converted to VARBINARY, and so each character -- first 0, then x, then F, and so on -- is "converted" to its hex representation:

Character   --  Hex value
  0               30
  x               78
  F               46
  F               46
  D               44
  8               38
  F               46
  F               46
  E               45
  0               30
  0               30
  0               30

So, the 0xFFD8FFE000 was seen as just a string of characters, just like "this is a test", instead of as a sequence of bytes.

Fortunately, the fix is quite simple: just add a "style" value of 1 for the optional 3rd parameter to CONVERT:

SELECT CONVERT(VARBINARY(MAX), '0xFFD8FFE000', 1);
-- 0xFFD8FFE000