SQL Server 2005 includes an undocumented function, sys.fn_varbintohexstr, that converts a hex value to a string representation of that hex value (0×3a becomes ‘0×3a’), but there is no function to go back from a hex string to a hex value. Below is something that does the trick quite nicely

CREATE FUNCTION dbo.HexStrToVarBin(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN 
    DECLARE @hex char(2), @i int, @count int, @b varbinary(8000) 
    SET @count = LEN(@hexstr) 
    SET @b = CAST(” as varbinary(1)) 
    IF SUBSTRING(@hexstr, 1, 2) = ‘0x’ 
        SET @i = 3 
    ELSE 
        SET @i = 1 
    WHILE (@i <= @count) 
     BEGIN 
        SET @hex = SUBSTRING(@hexstr, @i, 2) 
        SET @b = @b + 
                CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE ‘[0-9]‘ 
                    THEN CAST(SUBSTRING(@hex, 1, 1) as int) 
                    ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 as int) 
                END * 16 + 
                CASE WHEN SUBSTRING(@hex, 2, 1) LIKE ‘[0-9]‘ 
                    THEN CAST(SUBSTRING(@hex, 2, 1) as int) 
                    ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 as int) 
                END as binary(1)) 
        SET @i = @i + 2 
     END 
    RETURN @b
END
GO