13,150,178 members (29,241 online)
Tip/Trick
alternative version

#### Stats

19.4K views
5 bookmarked
Posted 18 Dec 2012

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

, 19 Jan 2013
 Rate this:
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.

## Introduction

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;
BEGIN
WHILE @counter < 100 BEGIN
INSERT INTO TestNumbers VALUES (@counter);
SET @counter = @counter + 1;
END;
END;   ```

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

```OriginalValue
-------------
1
2
4
8
16
32
64  ```

## 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.

References

## History

• November 18th, 2012: Tip created.

## Share

 Architect Finland
No Biography provided

## You may also be interested in...

 Pro Pro

 First Prev Next
 Solution for older versions of SQL server taksis22-Sep-14 22:37 taksis 22-Sep-14 22:37
 Other way Member 292501121-Jan-13 1:18 Member 2925011 21-Jan-13 1:18
 Simplify the request FrenchData20-Jan-13 0:23 FrenchData 20-Jan-13 0:23
 My vote of 3 Thanasis Ioannidis19-Jan-13 14:03 Thanasis Ioannidis 19-Jan-13 14:03
 Re: My vote of 3 Thanasis Ioannidis19-Jan-13 14:06 Thanasis Ioannidis 19-Jan-13 14:06
 Re: My vote of 3 Mika Wendelius19-Jan-13 21:30 Mika Wendelius 19-Jan-13 21:30
 Re: My vote of 3 Thanasis Ioannidis22-Jan-13 3:43 Thanasis Ioannidis 22-Jan-13 3:43
 Re: My vote of 3 Mika Wendelius22-Jan-13 7:35 Mika Wendelius 22-Jan-13 7:35
 My vote of 5 faruk19683019-Jan-13 7:24 faruk196830 19-Jan-13 7:24
 Re: My vote of 5 Mika Wendelius19-Jan-13 21:31 Mika Wendelius 19-Jan-13 21:31
 Last Visit: 31-Dec-99 18:00     Last Update: 25-Sep-17 12:46 Refresh 1