Problem solve Get help with specific problems with your technologies, process and projects.

Check the bits of a numeric field

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:

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)
          ,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

This was last published in November 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.