# 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)ASBEGIN 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; -- Resultbin----------------0111111111100000`
Tags: