cast varbinary max to varchar max

Question

question

benjoe asked May 14, '21

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.

tools sql2008r2 varchar

1

10 |1200

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

benjoe commented · May 13, 2021 at 09:11 PM

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 ·

Answer 1 · 2021-05-14T07:02:19Z

Kev Riley answered May 14, '21

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

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

question