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
—————————–
0x436176656D616E00

–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
—————————–
Caveman

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 )

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

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

Advertisements

9 Responses

Subscribe to comments with RSS.

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

    Anonymous

    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?

      Brian

      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

    Anonymous

    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

    Anonymous

    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

    thanks

    farzad

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: