CODEWITHSUNDEEP
sql-server
mohammad hoseinifar
mohammad hoseinifar

Reputation: 23

Convert hex string to binary SQL Server

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

( Column Type Is NVARCHAR(MAX) )

To This Value : 0x20E60E0175D4F44CD6F7947883DDD4D0

( Column Type Is Binary(16) )

Upvotes: 1

Views: 8050

Answers (1)

gofr1
gofr1

Reputation: 15977

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 docs MS.

Upvotes: 3

Related Questions