A colleague of mine asked for a simple example to get him up and running with the basics of bitmasking. Since I had gone to the effort to create the example I thought I might as well blog it :o).
This example uses the logical AND operator &.
First of all you need to define your table..
create table BitmaskDemo
(ID int Primary key
,Supermarket varchar(10)
,bitmask varbinary(1000)
)
and then secondly assign a numeric value to your data values. I have used numbers in the power of 2 in this example. This ensures that every value gets assigned a unique number and that the sum of these numbers will always generate a unique combination. So bananas and oranges = 5.
/*
values
1 = Banana 00000001
2 = Strawberries 00000010
4 = Oranges 00000100
8 = Apples 00001000
16 = plums 00010000
32 = pineapple 00100000
*/
We are now ready to populate the table with some data
Insert into Bitmaskdemo
values (1,'Tesco',7) -- Bananas,strawberries & oranges 00000111
Insert into Bitmaskdemo
values (2,'Sainsburys',31) -- Bananas, strawberries, oranges, apples & plums 00011111
Insert into Bitmaskdemo
values (3,'Morrissons',8) -- Apples 00001000
Insert into Bitmaskdemo
values (4,'Waitrose',24) -- Apples and plums 00011000
Insert into BitmaskDemo
values (5,'Aldi',25) -- Bananas, Apples and Plums 00011001
Insert into BitmaskDemo
values (6,'Somerfield',9) -- Bananas & Apples 00001001
Finally we can now ask some questions
/* Who sells strawberries */
Select *
from BitmaskDemo
where Bitmask & 2 = 2
/* Who sells Bananas & Plums */
Select *
from BitmaskDemo
where Bitmask & 17 = 17
/* Who sells Bananas & Plums */
Select *
from BitmaskDemo
where Bitmask & 17 = 17
/* Who doesn't sell bananas */
Select *
from BitmaskDemo
where Bitmask & 1 <> 1
/* Who Sells Bananas or Pineapples */
select *
from BitmaskDemo
where bitmask & 33 = 1
OR bitmask & 33 = 32
/* Who Sells Apples but does not sell plums */
select *
from BitmaskDemo
where bitmask & 24 = 8
AND bitmask & 24 <> 24
Hope this helps anyone else trying to get their head round bitmasking! If there are any other amendments or suggestions that people want to make.
Cheers, JRJ