cast varbinary max to varchar max

Question

cast varbinary max to varchar max

Benjoe 431

I want to cast a varchar(max) to varbinary(max) and get the results with the hex numbers in front. I am doing
"SELECT
cast(Content as varchar(max))
FROM [dbo].[employeetable]
where Id = 216"
It gives me the correct value ="504B030414000600080000002100C7AF3C0FA10"
but i want "0x504B030414000600080000002100C7AF3C0FA10"
so I need the hex value '0x' in front.

Benjoe 431 Reputation points

2021-05-13T21:09:53.087+00:00

I am using SQL Server 2008r2 and just tried doing
I have tried doing: SELECT cast('0x' + Content as varchar(max)) FROM [dbo].[employeetable] where Id = 216 But I getting error The data types varchar and varbinary(max) are incompatible in the add operator.

Accepted answer

1 additional answer

Your answer

Benjoe 431 Reputation points

2021-05-13T21:09:53.087+00:00

I am using SQL Server 2008r2 and just tried doing
I have tried doing: SELECT cast('0x' + Content as varchar(max)) FROM [dbo].[employeetable] where Id = 216 But I getting error The data types varchar and varbinary(max) are incompatible in the add operator.

Answer 1

Benjoe 431

I have managed to solved it myself.
SELECT '0x' + CAST(Content as varchar(max)) FROM [dbo].[employeetable] where Id = 216

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

To get 0x in front, you need to say:

SELECT convert(varchar(MAX), Content, 1)

But I get a little worried when you say that you get the correct result with cast, save for the 0x. The default formatting is that the bytes are interpreted as charcaters, so

SELECT cast(0x414243 AS varchar)

produces ABC.

Benjoe 431 Reputation points

2021-05-13T21:51:46.29+00:00

Hi
Thanks for your response. I don't want to convert it. The original value was varbinary(max) and I converted it to varchar(max). It gave me wrong value so I am trying to revert it back to it's original value., hence cast

Share via

cast varbinary max to varchar max

1 additional answer

Your answer