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?

Solution 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

Solution 2

What you are seeing is correct. You should be able to run the following to see that they conversion is happening correctly (basically, convert the varbinary value in your DB back to a VARCHAR):

SELECT CONVERT(varbinary(max),'0xFFD8FFE000')
SELECT CONVERT(varchar(100), 0x307846464438464645303030)

OR

SELECT CONVERT(varchar(100), CONVERT(varbinary(max),'0xFFD8FFE000'))

Note the lack of single quotes around the varbinary value - not needed in SQL Server

Comments

[Thomas Dorloff]

Hmm, funny. The thing is that I am hacking into an existing application and all other Images are starting with

[How 'bout a Fresca]

what does the data stored look like and what is the datatype? Do you have an example?