I went with tho old analogue approach. I find this easier to get my head around.
create or alter function [dbo].[getbitmap02](@gbm_in decimal(38,0) )
returns varchar(150)
as
begin
--
-- The maximum input number is 38 9s ( (10^38)-1 ), which gives a maximum 127 bit output string
--
declare @thenum decimal(38,0)
set @thenum = @gbm_in
declare @nxtpowof2 decimal(38,0)
declare @thestrng varchar(150)
declare @thestrng_50_0s varchar(50)
set @thestrng_50_0s = '00000000000000000000000000000000000000000000000000'
declare @thestrng_all_150_0s varchar(150)
set @thestrng_all_150_0s = concat( @thestrng_50_0s , @thestrng_50_0s , @thestrng_50_0s )
declare @bitcount_int smallint
--
-- First find the exponent of the target number, this will be of the form n.nnnnnnn
-- (unless the target number is an exact power of 2.) we are only interested
-- in the integer part of the exponent, as this is the highest power of 2 in the
-- target number.
set @bitcount_int = log( @thenum,2)
-- the first bit is always a 1, so might aswell initialise it that way
set @thestrng = '1'
-- Because sqlserver has difficulty differentiating between e.g.
-- 126 and 125.9999999999999999999999999, we have to adjust the value of
-- @nxtpowof2 to the highest power of 2 below the target value. we do this
-- by adjusting down the value of @bitcount_int.
-- if i try the power of 2 function with an exponent greater than 122 i get an error.
-- so i have to convert to decimal(38,0) and then multiply by the appropriate power
-- of 2.
-- 126 is the maximum power of 2 less than 9999999999999999999999999999999999999,
-- so this is the maximum power of 2 that we will have to cater for.
if @thenum <
case
when @bitcount_int <= 122 then power(2.0,@bitcount_int)
when @bitcount_int = 123 then cast( power(2.0,122) as decimal(38,0) ) *2
when @bitcount_int = 124 then cast( power(2.0,122) as decimal(38,0) ) *4
when @bitcount_int = 125 then cast( power(2.0,122) as decimal(38,0) ) *8
when @bitcount_int = 126 then cast( power(2.0,122) as decimal(38,0) ) *16
end
set @bitcount_int = @bitcount_int -1
-- as we have already set the 1st bit we have to reduce the target value
-- by the value of the first bit, whose exponent is held in @bitcount_int.
set @thenum = @thenum -
case
when @bitcount_int <= 122 then power(2.0,@bitcount_int)
when @bitcount_int = 123 then cast( power(2.0, 122) as decimal(38,0) ) *2
when @bitcount_int = 124 then cast( power(2.0, 122) as decimal(38,0) ) *4
when @bitcount_int = 125 then cast( power(2.0, 122) as decimal(38,0) ) *8
when @bitcount_int = 126 then cast( power(2.0, 122) as decimal(38,0) ) *16
end
-- once again, as we have already set the 1st bit we have to reduce the exponent of
-- @nxtpowof2 value by 1 i.e. we have to reduce the bitcount holder @bitcount_int by 1.
set @bitcount_int = @bitcount_int-1
-- sets up the next power of 2 to test for presence, and if so, subsequent subtraction.
set @nxtpowof2 =
case
when @bitcount_int <= 122 then power(2.0,@bitcount_int)
when @bitcount_int = 123 then cast( power(2.0, 122) as decimal(38,0) ) *2
when @bitcount_int = 124 then cast( power(2.0, 122) as decimal(38,0) ) *4
when @bitcount_int = 125 then cast( power(2.0, 122) as decimal(38,0) ) *8
when @bitcount_int = 126 then cast( power(2.0, 122) as decimal(38,0) ) *16
end
while @bitcount_int > -1
begin
-- if the target number becomes 0, all the remaining bits in the bitstring will be 0.
-- so set them and exit the loop.
if @thenum = 0
begin
-- if the target number becomes 0 we have to set the bitcount back up by 1 to get the correct
-- number of 0s
set @bitcount_int = @bitcount_int +1
set @thestrng = concat (@thestrng , substring (@thestrng_all_150_0s, 1, @bitcount_int ) )
break
end -- if @thenum = 0
-- if @thenum >= @nxtpowof2 ,then the next power of 2 is present so we add a 1 to the
-- bit string, and decrement the target number by that power of 2. otherwise the next
-- power of 2 is not present so we add a 0.
if @thenum>= @nxtpowof2
begin
set @thestrng = concat (@thestrng , '1' )
set @thenum = @thenum -
case
when @bitcount_int <= 122 then power(2.0,@bitcount_int)
when @bitcount_int = 123 then cast( power(2.0, 122) as decimal(38,0) ) *2
when @bitcount_int = 124 then cast( power(2.0, 122) as decimal(38,0) ) *4
when @bitcount_int = 125 then cast( power(2.0, 122) as decimal(38,0) ) *8
when @bitcount_int = 126 then cast( power(2.0, 122) as decimal(38,0) ) *16
end
end
else set @thestrng = concat (@thestrng , '0' )
-- sets up the next power of 2 to test for presence, and if so, subsequent subtraction.
set @bitcount_int = @bitcount_int -1
set @nxtpowof2 =
case
when @bitcount_int <= 122 then power(2.0,@bitcount_int)
when @bitcount_int = 123 then cast( power(2.0, 122) as decimal(38,0) ) *2
when @bitcount_int = 124 then cast( power(2.0, 122) as decimal(38,0) ) *4
when @bitcount_int = 125 then cast( power(2.0, 122) as decimal(38,0) ) *8
when @bitcount_int = 126 then cast( power(2.0, 122) as decimal(38,0) ) *16
end
end -- loop
return (@thestrng )
end
go
To verify results from above I run them through the function below.
create or alter function [dbo].[bitmap_getnum02](@bmgn_in varchar(200) )
returns decimal(38,0)
as
begin
declare @thenum decimal(38,0)
declare @thebit smallint
declare @thestrng varchar(200)
set @thestrng = @bmgn_in
declare @bitcount_int smallint
declare @count smallint
set @count = len (@thestrng)
set @bitcount_int = 1 -- @count
set @thenum = 0
-- get the first bit of the input string
set @thebit =
case when substring(@thestrng ,@bitcount_int,1) = '1' then 1
else 0
end
while @bitcount_int <= @count
begin
-- if the current bit is a 1, add the corresponding power of 2 to the total @thenum
if @thebit = 1
begin
if @count-@bitcount_int <= 122
set @thenum = @thenum + ( power( 2.0, (@count-@bitcount_int) ) )
else if @count-@bitcount_int = 123
set @thenum = @thenum + cast(power(2.0,122) as numeric(38,0) ) * 2
else if @count-@bitcount_int = 124
set @thenum = @thenum + cast(power(2.0,122) as numeric(38,0) ) * 4
else if @count-@bitcount_int = 125
set @thenum = @thenum + cast(power(2.0,122) as numeric(38,0) ) * 8
else if @count-@bitcount_int = 126
set @thenum = @thenum + cast(power(2.0,122) as numeric(38,0) ) * 16
end -- if @thebit = 1
-- move the bit pointer on by one
set @bitcount_int = @bitcount_int +1
-- if the remaining characters of the target bitstring contains all 0's (no 1's), exit the loop
if charindex ( '1', substring (@thestrng ,(@bitcount_int), (200-@bitcount_int) ) ) < 0.5
begin
break
end
-- get the next bit of the input string
set @thebit =
case when substring(@thestrng ,(@bitcount_int),1) = '1' then 1
else 0
end
end -- loop
-- any integer number n can be represented as a sequence of powers of 2 added together.
-- e.g. 23 = 1*2^4 +0*2^3 +1*2^2 +1*2^1 +1*2^0 or 10111 or approx 2 ^ 4.52356195605701287.
-- in the case of 23 ,the integer part of the exponent represents the highest bit in 10111,
-- and the decimal part represents the collection of bits that come after the first bit 0111.
return (@thenum)
end
go
