Guys, I’m a little stuck trying to get results that I require from a BIT field. Basically, it’s an active flag and has four BITS, so a value could be anything from 0 to 15. It is stored in a BIT(4) field
I have a few queries that work as expected such as:
This gets all fully active items
SELECT BIN(active) FROM table WHERE active = active & ~0
This gets all where BIT 2 is 0
SELECT BIN(active) FROM table WHERE active = active & ~2
These work fine, I can easily find where something is disabled, but what I can’t work out is how to find out when only that one is disabled. For example, that last query will return rows saying 0000, 0001, 0101, 1000, 1001, 1100 and 1101 since in all cases BIT 2 is 0. What I need to do is find all values where only BIT 2 is 0, so only 1101. I don’t want to code in a requirement for 1101 because if we ever add a fifth BIT then I’ll need all queries to then look at 11101.
I’m sure that the answer that I seek is a simple one but I’m overcomplicating it. Please help!