Cast a Hex String to varbinary in SQL Server

Question

Let say I have a varchar(100) call @hexString and I

SET @hexString ='0x47617279204a6f686e736f6e2032303230'

How do I cast this varchar to an actual hexidecimal 0x47617279204a6f686e736f6e2032303230(without the quotation mark)?

@mohan111 I know but I want to "cast" this varchar to a varbinary, like an actual sequence of hex numbers, instead of a string. — Dylan Czenski
– Dylan Czenski, Commented Nov 24, 2016 at 0:50
You want to cast that text without the presence of qoutation mark? — Vijunav Vastivch
– Vijunav Vastivch, Commented Nov 24, 2016 at 1:07

Solomon Rutzky · Accepted Answer · 2016-11-24 00:57:43Z

7

Use CONVERT with a "style" value of 1:

DECLARE @HexString VARCHAR(100);
SET @HexString = '0x47617279204a6f686e736f6e2032303230';

SELECT CONVERT(VARBINARY(100), @HexString, 1);

The "style" value is an optional 3rd parameter to the CONVERT function.

That returns the following as a VARBINARY type, not a string:

0x47617279204A6F686E736F6E2032303230

answered Nov 24, 2016 at 0:57

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1