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

Answer 1

Older code, SQL 7, sp_hexadecimal

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

SQL 2008, native