CODEWITHSUNDEEP
sql-server-2008-r2
AlexF11
AlexF11

Reputation: 243

Convert text to image data type

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?

Upvotes: 3

Views: 11425

Answers (2)

Sparrow
Sparrow

Reputation: 2583

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.

Upvotes: 1

Froopy
Froopy

Reputation: 369

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

Upvotes: 4

Related Questions