Microsoft, SQL, SQL Server, Technical

Query for Powers of 2 in SQL Server 2005

I had a table with two columns: ID and Description. The task was to display the description of all elements whose ID is a power of two. Here’s the query that let me accomplish that:

SELECT ID, Desc FROM tblBits WHERE ID > 0 AND ID & (ID-1) = 0

How does this work?

Every power of two when converted to binary has only one bit set.
For example: 4=100, 8=1000, 16=10000
And one digit less in binary looks like:
3 = 011, 7=0111, 15=01111

So an AND operation between {4,3}, {8,7}, {16,15} and so on will always yield a ‘0’

Advertisements

One thought on “Query for Powers of 2 in SQL Server 2005

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s