/*
Binary to Decimal
Zwei Teile:
1) Stored Function
2) CTE, ohne Function!
--MUSTER:
--select sysdba.fod_Int2Binary(5242881)
select sysdba.fod_Binary2Int('00000000010100000000000000000001', 17, 32)
*/
IF EXISTS (select name, * from sysobjects where name = 'fod_Binary2Int' and Type = 'FN') Drop function fod_Binary2Int
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <2008-01-22>
-- Description:
-- =============================================
CREATE FUNCTION fod_Binary2Int (@Folge as char(32), @Von as Int, @Bis as Int)
RETURNS Int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int
DECLARE @Zeichen char(1)
Declare @i int
SET @Result = 0
SET @i = @Von
WHILE (@i <= @Bis)
Begin
SET @Result = @Result * 2
SET @Zeichen = Substring(@Folge, @i, 1)
IF @Zeichen = '1'
SET @Result = @Result + 1
SET @i = @i + 1
End
-- Return the result of the function
RETURN @Result
END
GO
------------------------------------------------
/*
Binary to Decimal
Link: http://improve.dk/converting-between-base-2-10-and-16-in-t-sql/ ( robert matthew cook )
*/
;with table_64_bit as
(
select CONVERT(varchar(64), '0') as bit_check, CONVERT(bigint, 0) as decimal_value
union all
select CONVERT(varchar(64), '1%'), CONVERT(bigint, 1)
union all
select CONVERT(varchar(64), REPLACE(SPACE(LEN(bit_check)-1), ' ', '_') + '1%'), CONVERT(bigint, POWER(CONVERT(bigint, 2), LEN(bit_check)-1)) from table_64_bit where LEN(bit_check) between 2 and 63
)
select
from_binary, sum(decimal_value) as to_decimal
from table_64_bit
join (select '11101011001011010101111010000' as from_binary) as fake_table on reverse(from_binary) like bit_check
group by
from_binary;
