2

I have to admit, sometimes I struggle with what I visualise as the solution and map it in code, here is an example count the matching ON bits in 2 bitmasks, it should be a bitwise comparison and be lightning fast however to make it work I end up looping over a string array, kind of like the opposite of what I need to achieve when considering speed.

Here is the T-SQL code:

declare @p1Flags    tinyint = 1|4|8|16, --> enum flags
        @p2Flags    tinyint = 1|4|16|32


print 'Person 1:'+ [Utilities].[ToBinaryString](@p1Flags) 
print 'Person 2:'+ [Utilities].[ToBinaryString](@p2Flags) 


declare @ix int =1,
        @Person1 varchar(8) =reverse([Utilities].[ToBinaryString](@p1Flags)),
        @Person2  varchar(8) =reverse([Utilities].[ToBinaryString](@p2Flags)),
        @match bit =0,
        @matched int=0;

        --> compare only the range that can hold data
declare @lenPerson1 tinyint = len(@Person1) - Charindex('1',reverse(@Person1),0)+1  
      , @lenPerson2 tinyint = len(@Person2) - Charindex('1',reverse(@Person2),0)+1  

while @ix <= @lenPerson1 
begin
     if @ix > @lenPerson2
        break;

     set @match= iif (SUBSTRING(@Person1,@ix,1)=SUBSTRING(@Person2,@ix,1) and SUBSTRING(@Person1,@ix,1)='1',1,0)

     if  @match=1 --> same interest 
        set @matched=@matched+1

    set @ix= @ix+1
end;


declare @result decimal(7,4) = (1.*@matched)*100./(1.* @lenPerson1)
print '-------------------------------------------------------------------------'
print 'Matching bits in bitmap: '+ cast(@matched as varchar(2))+' of '+cast(@lenPerson1 as varchar(2))+ ' = ' +cast(@result as char(8)) +'%'

The generated test shows the result:

Person 1:00011101
Person 2:00110101
----------------------------------------------
Matching bits in bitmap: 3 of 5 = 60.0000 %

So, the concept works, now how to do it the right way?

Thanks for the help

Walter

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Walter Verhoeven
  • 621
  • 5
  • 7
  • 1
    The best way I know to compare bit masks is using the `xor` logical operator - whenever the bits are equal, the `xor` operation will return 0, otherwise it will return 1. the tsql operator for `xor` is `^`. I hope that will point you to the right direction. – Zohar Peled Aug 16 '15 at 08:15
  • Then you could use `[dbo].[DecimalToBinary]` function ([source](http://improve.dk/converting-between-base-2-10-and-16-in-t-sql/)) and `LEN(REPLACE(binString, '0', ''))` (ex. `LEN(REPLACE('101000', '0', ''))` -> 2 ) – Bogdan Sahlean Aug 16 '15 at 09:23
  • Hi Zohar, I am not seeing how the XOR will count matching bit's, can you show a sample? – Walter Verhoeven Aug 16 '15 at 09:55
  • Hi Bodan, the source for DecimalToBinary originally came from http://weblogs.asp.net/bdill/integer-based-bit-manipulation-sql – Walter Verhoeven Aug 16 '15 at 09:59

0 Answers0