question

Benjoe-2576 avatar image
0 Votes"
Benjoe-2576 asked Benjoe-2576 edited

cast varbinary max to varchar max

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.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
Benjoe-2576 avatar image
0 Votes"
Benjoe-2576 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Benjoe-2576 edited

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·