question

benjoe avatar image
benjoe asked

cast varbinary max to varchar max

I want to cast a varchar(max) to varbinary(max) and get the results with the hex numbers. 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.

toolssql2008r2varchar
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have updated the query

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 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Bit confused which way round you want to go. Question title is varbinary to varchar, but then you say you "want to cast a varchar(max) to varbinary(max)". Your query matches the question title, so I'll presume that!

Use the style option of the convert statement, so something like

SELECT convert(varchar(max), Content, 1) FROM [dbo].[employeetable] where Id = 216

See: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#binary-styles

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.