Issue converting image data type to string

  • I've done some research on this and have yet to fully understand why it's happening and how I can fix it (which brought me here). I have a table that stores plain text data as an image data type. I've attempted converting it to a varbinary, then to a (n)varchar but the data returned is a symbol and not the expected value.  There is a similar column in another table that handles thisconversion cleanly.

    select NodeID as ID, DateTime, Calls, Meters as ImgData, convert(varbinary(8000), Meters ) as ImgToVarbinary, convert(nvarchar(max), convert(varbinary(8000), Meters )) as ImgToVarbinaryToNvar

    from Node_Real_Time where NodeID = 68211

    Results
    ID DateTime  Calls ImgData   ImgToVarbinary  ImgToVarbinaryToNvar
    68211 2017-12-21 21:12:12.000 1076 0x340400003404... 0x340400003404... д

    Any thoughts? Thanks in advance...

  • Try converting back to varchar, rather than nvarchar? If your initial string was a varchar, you need to convert back to that. For example:
    SELECT CONVERT(nvarchar(100),CONVERT(varbinary,CONVERT(image,'sdfashgu3o4ggjkxchbvkjasgvftkj'))); --varchar to nvarchar
    SELECT CONVERT(varchar(100),CONVERT(varbinary,CONVERT(image,'sdfashgu3o4ggjkxchbvkjasgvftkj'))); --varchar to varchar

    Also, at least on SQL Server 2012 SP4, you can't CONVERT an nvarchar to image, thus this seems to imply more so that your original data type was varchar.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Yeah I tried converting to varchar instead with similar results. I know what the data consists of but starting to wonder if it's not stored as plain text.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply