Some time ago, I wrote an introductory post about bitwise operations in SQL Server. I had fully intended on writing a follow-up to that. Alas the opportunity has passed for the idea I was working on back then.
As luck would have it though, I encountered a new opportunity to share something on this topic. This one came to me by once again helping out in the forums. And, since I worked it out, I will be using the same problem posed in the forum and the solution I proposed.
First we need a little setup. Let’s create a simple table and populate that table with some data.
[codesyntax lang=”tsql”]
CREATE TABLE ColorPlate (ColorID INT PRIMARY KEY IDENTITY(1,1),ColorPlate VARCHAR(10), ColorType INT) INSERT INTO ColorPlate (ColorPlate,ColorType) Select 'Red',1 UNION ALL SELECT 'Blue',2 UNION ALL Select 'Yellow',4
[/codesyntax]
As I said, this setup is rather simple. The solution is not much more complex. However, before we get to the solution, we need to know what we need the solution to do. From this table, I need to be able to determine the primary colors that make up a different color based on input of an ID relating to that color. I know. I know. We don’t have all of the colors and their ColorTypes presented to us at this point – but let’s just go with it for a bit. I would imagine that the other colors and the number assigned to their colortype would be populated at some other time.
For now, we are only working with seven color variations – so any number from 1-7 is a valid input. How do we find all of the colors that are required for the number that we input? Well, we use some smoke and mirrors. Just kidding. Seriously though, we use bitwise operations as well as a neat trick called “cross apply.”
[codesyntax lang=”tsql”]
Declare @ColorType INT = 3 Select cp1.* From ColorPlate cp1 CROSS APPLY ColorPlate cp2 CROSS APPLY ColorPlate cp3 Where cp1.colortype & cp2.colortype & cp3.colortype & @ColorType <> 0 Order by ColorID;
[/codesyntax]
Do you see what is being done there? I have known values in this table of 1,2, and 4. I know that 7 is the max number I am allowing for input at this time. Because of that, I know that I need three values in order to arrive at a value of 7. Due to this requirement, I know I must Cross Apply the ColorPlate table twice beyond the first select from it. That will permit me to sum three values from the ColorPlate table.
Now that I have access to three possible values, I need to compare those values using the Bitwise And operator. This is denoted by ampersand ( & ). Note that the where clause checks each of the three tables as well as the variable. Then, I want to make sure that their bitand operation is not 0. Pretty slick eh?
Let’s put it to action. If I run the above query with a value of 6 for the @ColorType variable, I will get a two record result set. The results returned would be the primary colors for green (which are Blue and Yellow). If I use 7 for that same variable, I will get a three record result-set which would include red, blue and yellow.
This was a rather simple solution and scenario for a bitwise operation. There are plenty of other examples out there of how to use these types of solutions. Some more elaborate than others – but many good examples nonetheless.
I am interested in finding more solutions that involve these types of operations. Who knows, maybe I will even be able to remember the neat stuff I learned while writing the last article on the topic and be able to put that up before too long.
