How to convert hex string to binary in SQL Server 2005?

Question

How can i convert a hex string in SQL Server to binary?

Better yet, how can i convert a hex string in SQL Server to an integer?

The problem is that every existing answer on Stackoverflow assumes SQL Server 2008.

Failed attempts

• Convert hex string to binary SQL Server

  SELECT CONVERT(binary(16),'0x01',1)
  0x30783031000000000000000000000000
  

• SQL Server hex string to varbinary conversion

  select CONVERT(varbinary(max), '0x01', 1);
  0x30783031
  
  select CONVERT(varbinary(max), '01', 2);
  0x3031
  

• Convert integer to hex and hex to integer

  -- If the '0x' marker is present:
  SELECT CONVERT(INT, CONVERT(VARBINARY, '0x000001', 1))
  808464433
  
  -- If the '0x' marker is NOT present:
  SELECT CONVERT(INT, CONVERT(VARBINARY, '000001', 2))
  808464433
  

Edit

...yes 2005 has varbinary. Even 2000 has varbinary:

SELECT name, xtype FROM systypes WHERE name LIKE '%binary%'; 
SELECT @@version;

name       xtype
---------  -----
varbinary  165
binary     173

(No column name)
---------------------------------
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
May  3 2005 23:18:38 
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Even SQL Server 6.5 has varbinary. *(archive)*Interesting, and typical SO fashion, to try to circumvent the question rather than answer it.

Answer 1

Works for values that can be represented as a bigint

DECLARE @Hex VARCHAR(10)='0x3078'
DECLARE @DecValue BIGINT=0
DECLARE @Power TINYINT = 0

SET @Hex=REVERSE(REPLACE(@Hex,'0x',''))
WHILE LEN(@Hex)>0
BEGIN
    SET @DecValue=@DecValue+(POWER(16,@Power)*CONVERT(TINYINT,LEFT(@Hex,1)))
    SET @Power=@Power+1
    SET @Hex=RIGHT(@Hex,LEN(@Hex)-1)
END

SELECT @DecValue AS [Decimal value]

Answer 2

SQL Server 2005 had a function master.sys.fn_varbintohexstr() (apparently in SQL Server 2000 it was called dbo.fn_varbintohexstr) to convert binary to string.

Searching for this function name brings up answers for converting hex strings to binary, such as this SO answer, or Social MSDN (useful code, but lots of dead links), or this MSDN blog using XQuery to parse a hex string.