Convert hex string to binary SQL Server

Question

In Sql Server I want To Convert this Value : 20E60E0175D4F44CD6F7947883DDD4D0

( Column Type Is NVARCHAR(MAX) )

To This Value : 0x20E60E0175D4F44CD6F7947883DDD4D0

( Column Type Is Binary(16) )

Answer 1

You can try like this:

DECLARE @test TABLE (
    nvar nvarchar(max),
    bin16 binary(16)
)

INSERT INTO @test (nvar) VALUES
(N'20E60E0175D4F44CD6F7947883DDD4D0')

UPDATE @test
SET bin16 = CONVERT(binary(16),'0x'+nvar,1)

SELECT *
FROM @test

Output:

nvar                                bin16
20E60E0175D4F44CD6F7947883DDD4D0    0x20E60E0175D4F44CD6F7947883DDD4D0

Note: there is more info about CONVERT and style = 1 in a context of binary datatype here on MSDN.