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: common table expressions, t-sql programming, xml