Hexa to binary conversion

Reference: http://pratchev.blogspot.co.uk/2008/04/convert-hex-string-to-binary-string.html

CREATE FUNCTION dbo.Hex2Bin(@hex VARCHAR(256))

RETURNS VARCHAR(1024)

AS

BEGIN

DECLARE @bin VARCHAR(1024);

SET @bin = '';

WITH Converter (hex, bin)

AS

(SELECT '0', '0000' UNION ALL

 SELECT '1', '0001' UNION ALL

 SELECT '2', '0010' UNION ALL

 SELECT '3', '0011' UNION ALL

 SELECT '4', '0100' UNION ALL

 SELECT '5', '0101' UNION ALL

 SELECT '6', '0110' UNION ALL

 SELECT '7', '0111' UNION ALL

 SELECT '8', '1000' UNION ALL

 SELECT '9', '1001' UNION ALL

 SELECT 'A', '1010' UNION ALL

 SELECT 'B', '1011' UNION ALL

 SELECT 'C', '1100' UNION ALL

 SELECT 'D', '1101' UNION ALL

 SELECT 'E', '1110' UNION ALL

 SELECT 'F', '1111'),

N1 (n) AS (SELECT 1 UNION ALL SELECT 1),

N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),

N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),

N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)

           FROM N3 AS X, N3 AS Y)

SELECT @bin = (SELECT REPLACE(SUBSTRING(@hex, n, 1), hex, bin)

               FROM N4 AS Nums

               JOIN Converter AS C

                 ON SUBSTRING(@hex, n, 1) = hex

               WHERE n <= LEN(@hex)

               FOR XML PATH(''));

RETURN @bin;

END

GO

SELECT dbo.Hex2Bin('02') AS bin;