In Sql Server, how to convert binary strings to binary?

Question

I have some data in string format that represents binary data (e.g. '0x0002'). Is there some function or trick where I can convert these from literal strings to binary? That is, I want '0x0002' to become 0x0002, and SELECT CAST('0x0002' AS BINARY(20)) obviously won't do that. I did come up with some painfully slow process that involves building up SQL Statements and assigning them to a variable and executing that (e.g. "EXEC (@Query)"), but I'm looking for something where I don't have to do that.

If it helps, here is a sample table that you can test this on:

CREATE TABLE #T (BinaryString VARCHAR(100))
INSERT INTO #T VALUES('0x0000000000000000000000000000000000000002') -- Binary = the integer 2
INSERT INTO #T VALUES('0x000000000000000000000000000000000000007B') -- Binary = the integer 123

gbn · Accepted Answer · 2009-03-17 19:48:43Z

6

Older code, SQL 7, sp_hexadecimal

SQL 2005 (+ 2000 maybe), master.dbo.fn_varbintohexstr

SQL 2008, native

answered Mar 17, 2009 at 19:48

gbn

434k84 gold badges602 silver badges690 bronze badges

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

Collectives™ on Stack Overflow

In Sql Server, how to convert binary strings to binary?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related