Varbinary and image conversion

Question

I have an Access front end that links to a SQL Server backend.

There are 3 fields in a table that I am trying to convert to text from the backend:

o_name                varbinary(2000)  
O_PropertyBinary1     varbinary(2000)  
O_PropertyBinary2     image

I can convert the o_name field using:

convert(varchar([max]),[O_Name])

and that works fine.

e.g. 4153534554  = ASSET

However, what can I use for the other two fields, as it seems I can't convert an image field and converting the O_PropertyBinary1 comes out with garbage characters.

score 0 · Answer 1 · answered May 07 '13 at 05:03

0

The output is depended on the stored data an the appropriate conversion.
If the stored data is binary e.g. Bitmaps, converting to text will never give a usable result.
If data stored is text, it could be Varchar or NVarchar and kind conversion is depending.

in the example below VC_VB2NVarchar and VC_IMG2NVarchar would display your described garbage characters

Declare @tab Table(nvc NVarchar(100),vc Varchar(100)
,img image,vb VarBinary(200),img2 image,vb2 VarBinary(200))
Insert into @tab (nvc,vc) Values ('123456789','123456789')

Update @tab set vb=Convert(VarBinary(200),nvc),img=Convert(Image,Convert(Varbinary(max),nvc))
               ,vb2=Convert(VarBinary(200),vc),img2=Convert(Image,Convert(Varbinary(max),vc))
Select nvc,vc
      ,CONVERT(Nvarchar(100),vb) as NVC_VB2NVarchar
      ,CONVERT(Varchar(200),vb)  as NVC_VB2Varchar
      ,CONVERT(Nvarchar(100),Convert(VarBinary(max),img)) as NVC_IMG2NVarchar
      ,CONVERT(Varchar(200),Convert(VarBinary(max),img))  as NVC_IMG2Varchar
      ,CONVERT(Nvarchar(100),vb2) as VC_VB2NVarchar
      ,CONVERT(Varchar(200),vb2)  as VC_VB2Varchar
      ,CONVERT(Nvarchar(100),Convert(VarBinary(max),img2)) as VC_IMG2NVarchar
      ,CONVERT(Varchar(200),Convert(VarBinary(max),img2))  as VC_IMG2Varchar

from @Tab

answered May 07 '13 at 05:03

bummi

27,123
14
62
101

here is an example of the data: – user2356494 May 07 '13 at 22:32
010B0000002D01004010000000002E010040102705000030010040182C050000122720400840050000D52720403044050000D52B20405C45050000FF2F20401409060000113020405C77060000C8312040587B060000CA312040587F060000010020411083060000416E2065636F6E6F6D6963207265736F75726365207468 – user2356494 May 07 '13 at 22:33
when i convert this from hex to ascii the result is: ?????-??@?????.??@?'???0??@?,????' @?@????' @0D????+ @\E????/ @??????0 @\w????1 @X{????1 @X?????? A?????An economic resource th – user2356494 May 07 '13 at 22:33
i want to extract out just the bit 'An economic resource th' Do you know how I can do this? – user2356494 May 07 '13 at 22:34
You will need a known structure. If it is always identic you can do something like this: http://sqlfiddle.com/#!3/d41d8/13323 – bummi May 08 '13 at 05:20
how would i find the structure? would that be documented somewhere in the database and it seems to vary as well..... – user2356494 May 08 '13 at 23:58
does anyone know how I might be able to extract the text from this field? – user2356494 May 13 '13 at 06:48

Varbinary and image conversion

1 Answers1

Linked