Bitwise logic, it seems is one of those ancient areas of computing that’s only remembered by those old guys that have been kicking around for years. But what is it and how can we use it in SQL?
Before we start looking at bitwise logic and the bitwise operators, we need to have a quick reminder of binary and what’s that’s all about (if you’re happy with binary then you can skip the next section and get right to the meat).
Binary, WTF is that?!
I’m sure that we’re all happy with the idea that binary is some sort of funky coding that’s used by computers and is made up of ones and zeros. We know that each of those ones or zeros is called a bit and that there are eight bits that make up a byte. But what do they mean?
We’re all happy with a 10 base number system, trust me you are, you use it all the time. Basically every digit represents a power of 10. What that means for you and me is that the least significant digit represents a 1, the second digit represents a 10, the third a 100 and so on. So in the 10 base system that we’re all familiar with the number 241 could be explained as… the first digit is worth 1 one (1), the second digit is worth 4 tens (40) and the third digit 2 hundreds (200) so we know it’s two hundred and forty one.
Binary is a 2 base system, that means that the digits represent a power of 2. So in the case of binary, the least significant bit (LSB) is worth 1, the next 2, then 4 and so on.
But what’s that really mean? Well most people should know that the maximum number that you can store in a single byte is 255, which shown in binary happens to be 11111111.
Let’s take a look at that number for a second and think about what we know about binary.
128+64+32+16+8+4+2+1 = 255
So what is the value of byte, 11010010?
128+64+16+2 = 210
So now you know your binary, let’s have a look at bitwise logic.
Bitwise operators are operators that work on the individual bits of a value. Let’s take a little look at the three bitwise operators that we’ve got in SQL server (there is actually a fourth but I’ll talk about that one another time) and hopefully things will start making a bit of sense.
Bitwise AND (&)
The bitwise AND will compare each individual bit in a value with its corresponding bit in a second value, returning a 1 if both bits are 1. Take a look at the following examples using only single bits.
DECLARE @FirstBit BIT = 1 DECLARE @SecondBit BIT = 0 SELECT @FirstBit & @SecondBit
In this example, we’re saying, return a 1 if @FirstBit AND @SecondBit are 1. In this case you can see that @SecondBit is 0 so the result is 0.
How about this….
DECLARE @FirstBit BIT = 1 DECLARE @SecondBit BIT = 1 SELECT @FirstBit & @SecondBit
In this case as both are 1 so SQL Server would return 1.
DECLARE @FirstBit BIT = 0 DECLARE @SecondBit BIT = 0 SELECT @FirstBit & @SecondBit
Well, as neither bit is 1 then SQL Server will return us a 0.
Ok, getting the idea?
Let’s have a look at how we can apply this logic to an integer. Consider how the following would be evaluated…
DECLARE @FirstByte INT= 220 DECLARE @SecondByte INT= 177 SELECT @FirstByte & @SecondByte
Well the answer is 144, but how’s it come to that? To figure that out we need to take a look at those numbers in their binary form. 220 is 11011100 and 177 is 10110001. Now remember that the AND is applied to the corresponding bit, what that means is that the first bit on @FirstByte is evaluated against the first bit of @SecondByte, the second bit of @FirstByte is evaluated against the second bit of @SecondByte and so on.
That looks something like this…
So the returned value, in binary is 10010000 which surprise surprise, equates to 144.
Bitwise OR (|)
The bitwise OR works in a very similar way to the bitwise AND only that this time it’ll return a 1 if either of or both the bits in question are 1.
Lets change our earlier example slightly and replace the bitwise AND with a bitwise OR.
DECLARE @FirstByte INT= 220 DECLARE @SecondByte INT= 177 SELECT @FirstByte | @SecondByte
The answer we get now is, 253. Let’s break that down into binary and see what’s going on…
I’m sure that by now you wont be surprised to learn that 11111101 equates to 253.
Bitwise XOR (^)
So what’s the XOR? Exclusive OR will return a 1 of one or the other evaluated bytes are 1 BUT NOT if both are a 1.
DECLARE @FirstByte INT= 220 DECLARE @SecondByte INT= 177 SELECT @FirstByte ^ @SecondByte
The answer now is 109 or 01101101 in binary.
So I hope you’ve got a bit of an understanding of bitwise operators now (there is actually a fourth that we’ve not spoken about, bitwise NOT but that works slightly differently to the others so I may well cover than in another post.
Bitwise AND – Returns 1 if both bits evaluated are 1
Bitwise OR- Returns 1 if either or both bits are 1
Bitwise XOR- Returns 1 if one but NOT BOTH bits are 1
There are a bunch of things that bitwise logic can be really useful when dealing with things such as bitmaps and encryption, but that’s another exciting story and I might tell you all about it one day….