Caveman's Blog

My commitment to learning.

SQL Server: How to convert image data type to plain text and back?

with 9 comments

Following is a demonstration of one of the ways of using the SQL Convert function to convert image data type to plain text and back. FYI: Image data type is used to store binary data and can store variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes [1].

Microsoft advocates to avoid the further using of text, ntext and image data types since these data types will be removed from future versions of SQL Server. Further reading can be done about at Using Large-Value Data Types [2].

–The following example shows how plain text
–can be converted to image type data

SELECT CONVERT(binary(8), ‘Caveman’) AS ‘text to binary’

OUTPUT: text to binary

–The following example shows how image type data
–can be converted to Text

SELECT CONVERT(char(8), 0x436176656D616E00) AS ‘binary to text’

OUTPUT: binary to text

Tip: CAST vs CONVERT function
Both these functions can be used for the explicit conversion of an expression of a datatype to another [1]. CONVERT function is more powerful of the two functions in give us more flexibility during conversion.

Using CAST:
CAST ( expression AS data_type )

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

1. MSDN Online.
2. Using Large-Value Data Types


9 Responses

Subscribe to comments with RSS.

  1. Explicit conversion from data type image to char is not allowed


    August 28, 2009 at 1:36 pm

    • If your using VBScript/Classic ASP on the output you can use Response.BinaryWrite() to get text out of an image data type.

      Sorry if this seems irrelevant but i stumbled across this article while looking for that solution. Maybe someone else will too?


      October 6, 2010 at 10:41 am

  2. Hello i am trying to convert an image data type from sql to an avi format in c#, the image is converted from an avi file

    Please help


    March 8, 2010 at 7:29 am

  3. First convert image to varbinary(max) then to varchar(max) then back to image.

    convert(image,convert(varchar(max),convert(varbinary(max), [YourImageColumn]))) from TableA


    June 2, 2010 at 5:07 pm

  4. hi dear all
    this is last code is working well but some rows have error like this : {\rtf1\ansi\deff0{\fonttbl{\f0\fnil Arial;}}

    this error i think becuse of fonts ???
    how can i repair it



    June 9, 2010 at 4:28 am

  5. windows server 2003 standard edition…

    […]SQL Server: How to convert image data type to plain text and back? « Caveman's Blog[…]…

  6. Good blog post. I certainly appreciate this website.
    Continue the good work!

  7. Excellent! you save me for long hous of coding! tnx!!!!!!

    Javier Pichot

    November 18, 2013 at 9:32 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: