Welcome to EMC Consulting Blogs Sign in | Join | Help

Claypole's World - The SQL Server Side

Concerning development of bespoke applications and database administration.

Using a Bitmask - a practical example

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
Bitmask Demo Result 1
/* Who sells Bananas & Plums */
Select *
from BitmaskDemo
where Bitmask & 17 = 17

Bitmask Demo Result 2 

/* Who sells Bananas & Plums */
Select *
from BitmaskDemo
where Bitmask & 17 = 17

Bitmask Demo Result 3

/* Who doesn't sell bananas  */ 
Select * 
from BitmaskDemo 
where Bitmask & 1 <> 1 
Bitmask Demo Result 4
/* Who Sells Bananas or Pineapples */ 
select * 
from BitmaskDemo 
where bitmask & 33 = 1 
OR bitmask & 33 = 32 

Bitmask Demo Result 5

/* Who Sells Apples but does not sell plums */ 
select * 
from BitmaskDemo 
where bitmask & 24 = 8 
AND bitmask & 24 <> 24 

Bitmask Demo Result 6

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

 

Published Friday, July 04, 2008 11:28 PM by James.Rowland-Jones

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andy said:

If you need to do a bitshift or rotate operation then you can multiply or divide your bitmask by 2.

declare @intbit int

declare @intmask varbinary(1000)

select @intmask = bitmask from BitmaskDemo where Supermarket = 'Tesco'

set @intbit=1

while @intbit <= 6

begin

select @intmask % 2

set @intmask = @intmask / 2

set @intbit = @intbit + 1

end

July 7, 2008 11:02 AM
 

Claypole's World - The SQL Server Side said:

A couple of years ago I worked on an issue with my colleague Steve Wright about an issue with merge replication.&#160;

June 5, 2009 12:14 PM
 

Hugh said:

Thanks for the great resource.  I have a tiny bit to add.  The following example could be more simply expressed:

/* Who Sells Bananas or Pineapples */

select *

from BitmaskDemo

where bitmask & 33 = 1

OR bitmask & 33 = 32

You could write it like this:

select *

from BitmaskDemo

where bitmask & 33 != 0

September 21, 2010 4:16 PM
 

Michael P said:

I came across this looking at working on an Error Messages table.  This is a great example, but my only concern is using Bitmaksing like this, it appears the highest you could go (assuming you are using BIGINT) with be 2^62.  Anything past that would be outside of TSQL's range, and so I can only really reference 62 different error messages.  Is this correct, or am I missing something that would allow me more error messages?

November 29, 2012 5:49 PM
 

Tina said:

Thank you for the great example! First time reading about bit mask, very cool!

August 27, 2013 10:07 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About James.Rowland-Jones

James is an Advisory Practice Consultant with EMC Consulting. He works primarily with SQL Server technologies in architecture, development and administration capacities. He also co-ordinates our Internal SQL Server Community.

View James Rowland-Jones's profile on LinkedIn

Powered by Community Server (Personal Edition), by Telligent Systems