2

Table : FileData

Column     Datatype
------     --------
FiledataId int 
Value       nvarchar(max)

In the Value column, I put file name of file+|+ binary of File.

Example:
- Image example data: helloWorld.png|/9j/4AAQSkZJRgABAgAAAQAB.....
- File example data: 3_Templateexcel.xlsx|UEsDBBQABgAIAAAAIQCnlfmZhAEAABQGAAATAN0BW0NvbnRlbnRfV

Now, I would like to make a new table: Table : FileDataNew

Column      Datatype
------      --------
FileDataId int 
Name        nvarchar(255)
Value       Image

I would like to move data from FileData table to FileDataNew. But I have a problem when converting nvarchar data to image Field.

I tried solutions:

Solution one:

cast(cast(filedata as binary) as varchar(1000))
Solution two : 
 convert (convert (varchar(max) , convert (varbinary (max) , filedata ))
BUT it still DOES NOT work.

Is it possible to convert nvarchar data (/9j/4AAQSkZJRgABAgAAAQAB..... Or UEsDBBQABgAIAAAAIQCnlfmZhAEAABQGAAATA) to image field, and keep it still working?
Can you give me any ideas?
Thanks.

user3796752
  • 23
  • 1
  • 6
  • There's no such thing as an nvarchar file. What you posted is just text, not an image. If you tried to open it with a drawing program it wouldn't open. Most likely it's an encoded image. If you want to store it as a BLOB, you need to *decode it* in *your application* code. What *is* the encoding used? – Panagiotis Kanavos Aug 19 '16 at 09:29
  • You really shouldn't. [`image`](https://msdn.microsoft.com/en-GB/library/ms187993.aspx) is a deprecated type, and has been for over a decade. – Damien_The_Unbeliever Aug 19 '16 at 09:30
  • Damien, though you are right, the comment by @PanagiotisKanavos is still valid even if he uses `varbinary`. – Lasse V. Karlsen Aug 19 '16 at 09:42
  • If the text is Base64 encoded, you *can* decode it in T-SQL and store it in a `varbinary(max)` column with `SELECT CAST( '/9j/4AAQSkZJRgABAgAAAQAB' as XML ).value('.','varbinary(max)') ` as shown in [this duplicate question](http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql),eg `SELECT CAST( myBase64Column as XML ).value('.','varbinary(max)')` or `SELECT CAST( @myBase64Param as XML ).value('.','varbinary(max)')` – Panagiotis Kanavos Aug 19 '16 at 09:42
  • @user3796752 then you can't convert the encoded data. There are a lot of ways of encoding binary data as text. It looks like Base64 though. There are online Base64 decoders you could try to ensure it's Base64 – Panagiotis Kanavos Aug 19 '16 at 09:44
  • You keep saying "keep it working", but you have not told us what "it" is or what it means for "it" to "keep working". Can you please tell us why you want to add an image column when you already have the existing column, and what it means to "keep it working"? – Lasse V. Karlsen Aug 19 '16 at 09:54
  • the datatype conversion chart at https://msdn.microsoft.com/en-GB/library/ms187928.aspx would have told you that your cast and convert statements would not work, because direct conversion between these types is not allowed (because it would be meaningless) – ADyson Aug 19 '16 at 10:00
  • @ Lasse V. Karlsen : I have to convert to image field because customer told me that store binary in nvarchar takes more disk space (more than 3 times) as the storage of binary data in a column of the type image. So I want to find a solution. I tried some solutions(just testing with image), I don't see the image is showed in UI anymore. I really appreciate your help. – user3796752 Aug 19 '16 at 10:01
  • @user3796752 once you've done the conversion from base64 to image type, you'd then have to modify the code in whatever application is reading this data so it understands to read the image data type instead of text and deal with it appropriately. – ADyson Aug 19 '16 at 10:04
  • Thank you very much. I tried the solution from Panagiotis Kanavos : `SELECT CAST( @myBase64Param as XML ).value('.','varbinary(max)')` ,and it worked for me. Image file and document file(word or excel) worked as I expected. Thank you again. – user3796752 Aug 19 '16 at 10:45

1 Answers1

0

A Base64 string can be converted to an Image by using:

cast(N'' as xml).value('xs:base64Binary(sql:variable("@FinalContent"))', 'varbinary(max)')

Where @FinalContent is the Base64 string.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Jayadev
  • 1