Is it possible to check the bits of a numeric field in the WHERE condition of a SQL request? The only means that I found, for example if I want to test the bit 0, is:
SELECT * FROM MyTable WHERE NumField IN (1, 3, 5,...)
Is there another solution?
Yes, what you could do is convert the number to a string representation of its binary value.
In MySQL, there's a very handy function called CONV that will do this:
create table numbers ( numfield integer );
insert into numbers (numfield) values (0); insert into numbers (numfield) values (1); insert into numbers (numfield) values (2); insert into numbers (numfield) values (3); insert into numbers (numfield) values (937); insert into numbers (numfield) values (1234567);
select numfield , conv(numfield,10,2) as bitstr from numbers
numfield bitstr 0 0 1 1 2 10 3 11 937 1110101001 1234567 100101101011010000111
Now all that's left to do is decide which bit position you want to test. If it's the right-most position, you can use the RIGHT function:
select numfield , conv(numfield,10,2) as bitstr from numbers where right(conv(numfield,10,2),1) = '0'
numfield bitstr 0 0 2 10
However, if it's any other position, you will have to figure out the position by using the LENGTH function and counting backwards. Complicating this is the fact that the string might be shorter than you expect, so you'll have to concatenate enough zeroes on to the front of it. For example, to test whether the bit in the eighth position from the right is on, you have start at the end, which is given by the LENGTH function, and them move over 7 positions to the left:
select numfield , conv(numfield,10,2) as bitstr from numbers where mid('0000000'+conv(numfield,10,2) ,length('0000000'+conv(numfield,10,2))-7 ,1) = '1'
numfield bitstr 937 1110101001 1234567 100101101011010000111
Other database systems may not have conversion functions exactly like MySQL's CONV. Look for CAST or CONVERT, and check the documentation to see whether you can convert numeric to binary and then binary to character string.
One final note: if you are indeed checking the rightmost bit position, as in your example, then you could also just divide by 2 and see if there's a remainder:
select numfield from numbers where mod(numfield,2) = 1
numfield 1 3 937 1234567
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.