0

We are trying to convert the column of type image to column of type varchar(max). We tried the below way to do the conversion.

--col1 is of type image
--col2 is of type varchar(max)

set col2 = convert(varchar(max), convert(varbinary(max), col1)) from table

When we do it, we are getting junk characters like ÿÿÿÿÿÿÿÿ. We also tried using nvarchar(max) instead of varchar(max) which again adds more junk data.

The above conversion works for few types of record and doesn't work for few.

col1 starting with "0xD0CF11E0A" is not working
col1 starting with "0x7B5C727" is working fine

Please help me identify the issue here.

2

If you are converting data from one format to another, then there is no such thing as "junk": it is all data, even if it doesn't "look" right. So, if you are saying that the conversion is not producing the expected results, then you need to be clearer about what the expect results should look like. You do mention that:

col1 starting with "0x7B5C727" is working fine

Well, doing that conversion

SELECT CONVERT(VARCHAR(MAX), 0x7B5C72);

Returns:

{\r

Why is "{\r" considered "working"? If the data in col1 is actual binary data, then you shouldn't be converting it to a string type in the first place. If "{\r" is somehow the expected result, then the fact that

SELECT CONVERT(VARCHAR(MAX), 0xD0CF11E0);

returns:

ÐÏà

would need to also be considered "correct".

Now, there is a minor difference between the two: the "working" input of "0x7B5C727" shows characters that all fall within the standard ASCII set of values 0 - 127 (i.e. 0x00 - 0x7F). But the "not working" input of "0xD0CF11E0" shows characters that mostly fall within the Extended ASCII range of 128 - 255 (i.e. 0x80 - 0xFF) which is determined by the Code Page associated with the default Collation of the database that the query is running in. And, the one character in the standard range -- 0x11 -- is a control character (hence the box). But, while the characters returned for the "not working" set can change per each Code Page, I tried several and they tend to mostly map to the same characters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.