RoR RoR - 2 years ago 116
SQL Question

SQL - Integer Type - Detect bit

With SQL, I would like to detect if a certain bit is on with an integer column type (MySQL)?

How would I go about this?

Basically, I want to detect if a bit is on, if it is on, then ignore.

I am using something like this now :

WHERE (column & 2) != 2

Answer Source

Say you're checking for the 3rd bit then...

SELECT bits & 8 = 8 FROM table;

This returns a binary (1 or 0) for whether the bit in question (in this case the third) is on or off for each row in the column 'bits'.

using 2^x for the xth bit instead of 8.

for example, to check the 5th bit we would use 2^5 = 32

SELECT bits & 32 = 32 FROM table;

However, this is needlessly complicated. Simply use boolean values in several columns and it will make things much easier.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download