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

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
—————————–
0×436176656D616E00

–The following example shows how image type data
–can be converted to Text
SELECT CONVERT(char(8), 0×436176656D616E00) 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

---

Possibly related posts: (automatically generated)

• What to do with text, ntext and image data types in sql
• SQL Server 2005 – varchar(max)