When you store a .jpg file in a varbinary(max) column and then view the data in SQL Server Management Studio, it appears like "0xFFD8FFE000..."

Is this Hex Encoded or something? What is SQL Server doing to generate this string? Can it be converted to a valid JPG file?


It's binary data with some additional Hex characters for padding, etc per the Microsoft Docs: binary and varbinary

Padding is achieved by using hexadecimal zeros.

  • can it be converted into a valid JPG file? – Mike W Nov 18 '20 at 13:57
  • @MikeW Yes it can. I'm not entirely sure how to do so (would require a conversion and handling of the padding) but here's a document that's probably relevant to converting: mssqltips.com/sqlservertip/4963/… – J.D. Nov 18 '20 at 14:46
  • 1
    JD - you could encapsulate some of the details from that link into your answer, along with a link to the original to make your answer more helpful. – Hannah Vernon Nov 18 '20 at 20:46

It appears that SSMS is converting the binary data to VARCHAR before displaying it.

  • The data is stored in varbinary columns is stored in binary format. When you select the data in SSMS, it is not converted into a varchar, it is shown using the hexadecimal representation of the binary data. i.e every two digits after the leading 0x is a hexadecimal number between 00 and FF (0 to 255 decimal). It's an array of bytes. – Hannah Vernon Nov 18 '20 at 20:19
  • isn't that the same as converting to varchar? try it and see... CONVERT(VARCHAR(MAX), imagedata, 1), – Mike W Nov 18 '20 at 20:29
  • well, not really. If you just go SELECT imagedata you'll get the same output. I mean SSMS could display it in binary form (i.e. 1's and 0's) or each byte could be converted to it's ASCII equivalent, but a hexadecimal byte array makes most sense. – Hannah Vernon Nov 18 '20 at 20:36
  • Did you downvote my answer? – Mike W Nov 18 '20 at 20:39
  • 1
    Frankly, you could just add that detail to your question. – Hannah Vernon Nov 18 '20 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.