Hexadecimal valued string to varbinary SQL Server 2008 [duplicate]

Question

I have a string with a hexadecimal value, as an example, like so: "AD5829FC..." which is a varbinary I took and saved in hexadecimal to a text file. The thing is, I need to get this back to a varbinary by running an insert query through C# and SQL Server 2008.

How do I get it back to its original format. I am currently using a SQL CAST like so:

CAST('HEX VALUE' AS varbinary(MAX))

NOTE: I need to save it to the text file like I just showed it so that the CSV doesn't get any \n or comma characters from a sequence in the hexadecimal

Answer 1

Here is example of T-SQL code to convert your string back to varbinary(max)

declare @hexstring varchar(max)='abcedf012439112200AABBCCFF';
set @hexstring = '0x'+@hexstring;
select CONVERT(varbinary(max), @hexstring, 1);