Retrieving text stored as an Image data type in SQL Server

So, you have an old database that relies on the Image data type to story rich text, and within the confines of SQL Management Studio, you want to read the contents. Turns out that the Image data type cannot convert to a varchar directly, like some of the newer binary data types can.

So you have to first translate it to binary and then convert that to varchar.


SELECT CONVERT(VARCHAR(8000),CONVERT(VARBINARY(8000),notepad_textimage)) from notepad


Incidentally, to get the size of the image data, use DATALENGTH (instead of LEN).