Convert text to image data type

Question

I have a table that for some reason stores text as IMAGE. I can grab the data and read it using

SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), column,2)) FROM table

Now I need to insert data back in to the table. I've tried

SELECT CONVERT(IMAGE, CAST('TEST TEXT' AS VARBINARY(MAX)))

But when I test converting it back using

SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), CONVERT(IMAGE, CAST('TEST TEXT' AS VARBINARY(MAX))),2))

It returns 䕔呓吠塅 which is obviously not right as it should return "TEST TEXT"

What am I doing wrong here?

Answer 1

The text you're trying to store is encoded as binary ASCII characters. You're trying to convert it back into a Unicode text string, which isn't what it originally was, therefore you're getting back garbled text.

Change your source text string into a Unicode string by adding N in front of it:

SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), CONVERT(IMAGE, CAST(N'TEST TEXT' AS VARBINARY(MAX))),2))

It should return the correct text. Tested this on SQL Server 2008

Answer 2

You can use this one:

SELECT CONVERT(**VARCHAR(MAX)**, CONVERT(VARBINARY(MAX), **CAST('TEST TEXT' AS IMAGE)**,**0**))

Basically, you were not consistent with your character type conversions. In some parts you used NVarChar and some parts Varchar. Also, the number 2 at the end is affecting the result. In you Convert statements, when you don't specify the code, default value (0) is used. So if you are converting it back, you should use the same code.