Click here to Skip to main content
11,795,848 members (82,390 online)
Click here to Skip to main content

Check if a number is a power of 2 with Sql Server 2012

, 19 Jan 2013 CPOL 13.3K 31 5
Rate this:
Please Sign up or sign in to vote.
This tip shows how to get rows from a table where a field contains a number that is a power of 2 using LOG function.


Sometimes there's a need to get rows from a table where a numeric field contains values for example a powers of 2. The satisfying rows would contain numbers such as 1, 2, 4, 8, 16 and so forth.  

With previous versions of Sql Server this could be done for example with a user defined function. Since Sql Server 2012 contains a new version for LOG function where the base can be defined separately, such query can be simplified.

So how...  

First let's create a small test table which will contain test numbers 

-- Create the test table
CREATE TABLE TestNumbers (
   SomeField INT

And fill it with some amount of values 

-- Add some numbers
DECLARE @counter AS INT = 1;
   WHILE @counter < 100 BEGIN
      INSERT INTO TestNumbers VALUES (@counter);
	  SET @counter = @counter + 1;

Now, the idea is to check if the return value of the LOG function with base of 2 contains an integer without any decimals. If this is true, the number is a power of 2. 

First, lets have a look at the values   

-- Test the logarithm function
SELECT SomeField                    AS OriginalValue,
       LOG(SomeField, 2)            AS Logarithm,
       ROUND( LOG(SomeField, 2), 0) AS RoundedLogarithm
FROM TestNumbers;    

The query above should return results like the following

OriginalValue   Logarithm          RoundedLogarithm
-------------   ----------------   ----------------
1               0                  0
2               1                  1
3               1,58496250072116   2
4               2                  2
5               2,32192809488736   2
6               2,58496250072116   3
7               2,8073549220576    3
8               3                  3
9               3,16992500144231   3
10              3,32192809488736   3
11              3,4594316186373    3
12              3,58496250072116   4
13              3,70043971814109   4
14              3,8073549220576    4
15              3,90689059560852   4
16              4                  4
17              4,08746284125034   4

As you can see the values of the logarithm and the rounded logarithm are exactly the same only when the original number is a power of 2 

So let's convert this to a condition 

-- Get powers of 2
SELECT SomeField AS OriginalValue
FROM   TestNumbers
WHERE  LOG(SomeField, 2) = ROUND( LOG(SomeField, 2), 0);  

The query above now returns the rows where the logarithmic and the rounded values match exactly so the result is 


The scripts used in the tip are included in the download.  

Points of Interest  

Note that if there's a value of 0 present in the database, that would result to a floating point error. So you may need to either exclude zeroes or change them to some other value in the query, which ever suits better for the situation.  



  • November 18th, 2012: Tip created.  


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Mika Wendelius
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.

However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).

For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

You may also be interested in...

Comments and Discussions

SuggestionSolution for older versions of SQL server Pin
taksis22-Sep-14 22:37
membertaksis22-Sep-14 22:37 
SuggestionOther way Pin
Member 292501121-Jan-13 1:18
memberMember 292501121-Jan-13 1:18 
SuggestionSimplify the request Pin
FrenchData20-Jan-13 0:23
memberFrenchData20-Jan-13 0:23 
GeneralMy vote of 3 Pin
Thanasis Ioannidis19-Jan-13 14:03
memberThanasis Ioannidis19-Jan-13 14:03 
GeneralRe: My vote of 3 Pin
Thanasis Ioannidis19-Jan-13 14:06
memberThanasis Ioannidis19-Jan-13 14:06 
GeneralRe: My vote of 3 Pin
Mika Wendelius19-Jan-13 21:30
mvpMika Wendelius19-Jan-13 21:30 
GeneralRe: My vote of 3 Pin
Thanasis Ioannidis22-Jan-13 3:43
memberThanasis Ioannidis22-Jan-13 3:43 
GeneralRe: My vote of 3 Pin
Mika Wendelius22-Jan-13 7:35
mvpMika Wendelius22-Jan-13 7:35 
GeneralMy vote of 5 Pin
faruk19683019-Jan-13 7:24
memberfaruk19683019-Jan-13 7:24 
GeneralRe: My vote of 5 Pin
Mika Wendelius19-Jan-13 21:31
mvpMika Wendelius19-Jan-13 21:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151002.1 | Last Updated 20 Jan 2013
Article Copyright 2012 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid