Convert Hex String to Binary String

Here is another use of table with numbers and concatenation using FOR XML PATH. Given a hexadecimal string value like ‘7FE0’, convert it to the binary representation ‘0111111111100000’. The table with numbers generated by the CTE is used to slice the hex string, then replace it with the corresponding binary value and concatenate using FOR XML PATH with blank element.

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('7FE0') AS bin;
 
-- Result
bin
----------------
0111111111100000