Post reply

How to convert Image datatype to String in MSSQL?

  • August 4, 2015 at 6:30 am

    #319435

    Hi All,

    I have an Image data which I need to convert to string data.

    Tried "Select CAST(CONVERT(nchar(1000),CAST( body AS varBINARY(1000) )) as VARCHAR(1000)) as TD

    FROM Table" , but it returns a set of question marks. (ex: ????????????(?????????????????????†???????????????????????????????????????????????????????????????????????????????8??????????????????...)

    Please let me know what is the proper way to do this! Thanks in advance 🙂

  • August 4, 2015 at 7:05 am

    #1817092

    Why? What do you expect to get? The varbinary is the correct way to keep an image on SQL Server.

    EDIT: You're getting all the question marks because you use varchar instead of nvarchar to handle unicode characters.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 4, 2015 at 7:15 am

    #1817097

    Please ignore the previous query that I tried.

    I tried this " select (CONVERT(VARCHAR(MAX),(convert(varbinary(max),body)))) from Table " and I got 86 records whereas there are more than 2000 records present in the 'Body'.

    Thanks!

  • August 4, 2015 at 7:16 am

    #1817098

    If you need to send an image to HTML, XML etc you have to use some sort of string encoding. Here is an example of Base64:

    http://blog.falafel.com/t-sql-easy-base64-encoding-and-decoding/

  • August 4, 2015 at 7:21 am

    #1817104

    nsca.dev (8/4/2015)

    Please ignore the previous query that I tried.

    I tried this " select (CONVERT(VARCHAR(MAX),(convert(varbinary(max),body)))) from Table " and I got 86 records whereas there are more than 2000 records present in the 'Body'.

    Thanks!

    Conversions won't limit the number of rows, something else is preventing that all the rows are returned.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

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