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

Labels: , ,