Convert Hex Data in an Image Field to String-CodePudding

We have a database hosted on our SQL Server 2016 server that has email content stored in an IMAGE type column. I am trying to convert the data to its string representation. Everything I've read says that I should be able to just use CONVERT(VARCHAR(MAX), Field, 1) but that's not working. I've tried the following as a test:

declare @body varbinary(max);

select @body = substring(cast(body as varbinary(max)),1,13) from dbo.arc_bodies where id = -2147467502

select @body, convert(varchar(1000), @body, 1), convert(varchar(1000), 0x3C68746D6C3E)

So I take the first 13 bytes (just for testing I kept the data short instead of grabbing the whole column) from the "body" IMAGE field and convert it to VARBINARY. In the last statement, I show the contents of @body, try the convert, and try the convert by just copying the raw data.

This is what I end up with:

(No column name)                (No column name)                (No column name)
0x3C68746D6C3E0D0A3C68656164    0x3C68746D6C3E0D0A3C68656164    <html>

The convert of the @body returns the exact same data as the raw binary but the convert of the raw data works. What am I doing wrong?

CodePudding user response：

In 3rd column in the last query you don't use any style. Just do the same for the 2nd one:

select @body, convert(varchar(1000), @body), convert(varchar(1000), 0x3C68746D6C3E)

A reference to MS docs