by Amol
21. February 2011 19:18
CREATE FUNCTION [dbo].[GetDecimal] (@BinaryNumber BIGINT)
RETURNS BIGINT
AS
BEGIN
DECLARE @DecimalNumber BIGINT
,@Divider BIGINT
,@BinaryNumberLength INT
SET @BinaryNumberLength = LEN(@BinaryNumber) - 1
SET @Divider = '1' + REPLICATE('0', @BinaryNumberLength);
WITH cte (Id, Divider, lvl)
AS
(
SELECT
@BinaryNumber / @Divider AS Id
,@Divider/10 AS Divider
,@BinaryNumberLength AS lvl
UNION ALL
SELECT
@BinaryNumber / Divider AS Id
,Divider/10 AS Divider
,lvl - 1
FROM cte
WHERE Divider > 0
)
SELECT @DecimalNumber = SUM(RIGHT(Id, 1) * POWER(2, Lvl))
FROM cte
RETURN (@DecimalNumber)
END