calculating bit flag intersection for congruency

Question

Technical circumstances:

Given is an int column in SQL Server 2008 R2 for saving decimal-“encoded” bit flags (ranging from 2⁰ to 2³⁰, thus having 31 available flags with the maximum being 1073741824). This column may comprise the entire range of allocatable integer combinations, all to categorize a single object.

Problem to be solved:

From a finite quantity of decimal “bit integers”, we have to find a single number that represents some sort of intersection of all those flags – like when you have 3,10,524290, it’s obvious that 2 should be set in the result, while the other bits (1,8,524288) are disputable. This is a very basic example. This could be a real set of input data (first two columns only):

Occurences | Decimal bit field | Binary representation
     7     |   268435460       | 10000000000000000000000000100
     5     |   268435488       | 10000000000000000000000100000
     5     |         128       | 00000000000000000000010000000
     4     |          32       | 00000000000000000000000100000
     3     |           4       | 00000000000000000000000000100
     3     |   268435492       | 10000000000000000000000100100
     2     |          36       | 00000000000000000000000100100
     2     |         132       | 00000000000000000000010000100
     1     |         160       | 00000000000000000000010100000
   Occurences of particular bit: 3--------------------3-6--6--
     Desired output possibility: 10000000000000000000000100100

Solution so far:

… implemented in Transact-SQL:

1. Gather all the integers to be evaluated and concatenate them into a comma-separated string.
2. Reducing the string, taking out the first number, looping through:
3. Checking testee against maximum value (&-AND) and dividing the maximum by 2 (while >=1).

… so far? ó.Ò

Now I have an output of binary representatives for the bits set. I consider saving those bits into a 31-column temporary table to go one with evaluation. But then I came to think: Isn’t there a more clever way to do this? SQL Server is super-fast, even when disassembling 10000 generated integers. But perhaps there’s a built-in function for calculating the “distance” between two binary bit flags.

I admit it’s an elaborate problem, but I really do see the light at the end of the tunnel, even when it needs to be done the circumstantial way. It’ll get even more complex, since a weighting should also be applied later on, so that 2× ¬00010000 @ 100% significance > 4× 00010000 @ 40% significance. But I’ll try to deal with this when I have a summary generated and available

Answer 1

You can use bitwise and for your operations

declare @i int
declare @x int
declare @cnt int
select @i=2147483647  -- 1111111 11111111 11111111 11111111
declare @t table (a int)
insert into @t values( 3),(10),(524290);

Select @i= (@i & a) from @t 


-- just for fun an output
set @x=1
set @cnt=0
While @cnt<31
  begin
  Print Case when @x & @i <> 0 then 'X' else ' ' end  +' ' + Cast(@x as Varchar(12))   
  Set @cnt=@cnt + 1
  if @cnt<31  Set @x=@x*2
  end 

or with a nicer output

declare @i int
declare @x int
declare @cnt int
select @i=2147483647  -- 1111111 11111111 11111111 11111111
Declare  @ref table(ref int) 
set @x=1
set @cnt=0
While @cnt<31
  begin
  insert into @ref Values(@x)
  Set @cnt=@cnt + 1
  if @cnt<31  Set @x=@x*2
  end 


declare @t table (a int)
insert into @t values( 3),(10),(524290);

Select @i= (@i & a) from @t 

Select * from @ref where ref&@i<>0 

as answer to your comment

declare @i int
declare @x int
declare @cnt int
select @i=2147483647  -- 1111111 11111111 11111111 11111111
Declare  @ref table(ref int) 
set @x=1
set @cnt=0
While @cnt<31
  begin
  insert into @ref Values(@x)
  Set @cnt=@cnt + 1
  if @cnt<31  Set @x=@x*2
  end 


declare @t table (a int)
insert into @t values( 3),(5),(9),(17),(33),(65),(128);

Select @i= (@i & a) from @t 

Select a,Count(*) as BitCount
from @ref r
join @t t on t.a & r.ref<>0
group by a

Select ref,Count(*) as OCC from @ref r
join @t t on t.a & r.ref<>0
group by ref

Select ref,(Select count(*) from @t where a & r.ref<>0) as OCC
from @ref r