Schlagwort: Wandeln

/*
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;

/*
Integer => Binär 
Zwei Varianten:
1) Stored Function
2) T-SQL, ohne Function

--MUSTER:
select sysdba.fod_Int2Binary(5242881)
--select sysdba.fod_Binary2Int('00000000010100000000000000000001', 17, 32)
*/


IF EXISTS (select name, * from sysobjects where name = 'fod_Int2Binary' and Type = 'FN') Drop function fod_Int2Binary
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date:	<2008-01-22>
-- Description:	
-- =============================================
CREATE FUNCTION fod_Int2Binary (@Zahl as Int)
--, @PreNulls as char(1)) 
RETURNS char(32)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result char(32)
	Declare @Rest int
	Declare @Anteil int
	
	SET @Result = ''
	SET @Anteil = @Zahl
	WHILE (@Anteil > 0)
	Begin
		Set @Rest = @Anteil % 2
		SET @Anteil = @Anteil / 2
		Set @Result = ltrim(str(@Rest)) + @Result
	End
	
	--If (@PreNulls = 'T')
		Set @Result = right('0000000000' + '0000000000' + '0000000000' + '00' + rtrim(ltrim(@Result)) , 32)

	-- Return the result of the function
	RETURN @Result
END
GO




---------------------------------------------------------



Declare @i as bigint = 5242881    --> 00000000010100000000000000000001
Select 
        CASE WHEN CONVERT(VARCHAR(16), @i & Cast(2147483648 as bigint) ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & Cast(1073741824 as bigint) ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 536870912 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 268435456 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 134217728 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 67108864 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 33554432 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 16777216 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 8388608 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 4194304 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 2097152 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 1048576 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 524288 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 262144 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 131072 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 65536 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 32768 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i & 16384 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  8192 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  4096 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  2048 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &  1024 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   512 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   256 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &   128 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    64 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    32 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &    16 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     8 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     4 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     2 ) > 0 THEN '1' ELSE '0'   END +
        CASE WHEN CONVERT(VARCHAR(16), @i &     1 ) > 0 THEN '1' ELSE '0'   END