SQL statement for SQL Server that Selects entry's from a column witch contains a value sum of power of 2

user_6102848 0
Viewed 357

I wish to write an SQL statement for SQL Server that Selects entry's from a column witch contains a value sum of power of 2 (2^n), for instance I have TABLE xxx:

_________________________________________________________
| id | col_pow |col_pow=sum of element power of 2 (2^n) |
|----|---------|----------------------------------------|
| 1  | 11      |11  = 8+2+1   = 2^3 + 2^1 +2^0          |           
| 2  | 3       |3   = 2+1     = 2^1 + 2^0               |
| 3  | 514     |514 = 512+2   = 2^9 + 2^1               |
| 4  | 49      |49  = 32+16+1 = 2^5 + 2^4 +2^0          |
| 5  | 7       |7   = 4+2+1   = 2^2 + 2^1 +2^0          |
---------------------------------------------------------

EX. SELECT

1) SELECT id TABLE xxx WHERE col_pow can_contains 4 (=2^2)

  • expected result:
| id |
|----|
| 5  |

2) SELECT id TABLE xxx WHERE col_pow can_contains 2 (=2^1)

  • expected result:
| id |
|----|
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |

3) SELECT id TABLE xxx WHERE col_pow can_contains 512(=2^9)

  • expected result:
| id |
|----|
| 3  |

How to write the 3 statements?

bit-manipulation tsql sql-server sql
2 Answers

If I understand you correctly, using Bitwise AND (&) may help here.

Table:

CREATE TABLE #Data (
    id int,
    col_pow int
)
INSERT INTO #Data 
    (id, col_pow)
VALUES
    (1, 11),
    (2, 3),
    (3, 514),
    (4, 49),
    (5, 7)

Statement:

DECLARE @power int = 9
SELECT *
FROM #Data
WHERE (col_pow & POWER(2, @power)) = POWER(2, @power)

Result:

id  col_pow
3   514

Calculations (for 2^9):

-------|----------------|-----
A      | 0010 0000 0010 | 514
B      | 0010 0000 0000 | 512
-------|----------------|-----
A & B  | 0010 0000 0000 | 512
user_65780800

You can use integer arithmetic as well

declare @p int = 1;

select *
from (
    values
     (1, 11  , '= 8+2+1   = 2^3 + 2^1 +2^0')
    ,(2, 3   , '= 2+1     = 2^1 + 2^0     ')
    ,(3, 514 , '= 512+2   = 2^9 + 2^1     ')
    ,(4, 49  , '= 32+16+1 = 2^5 + 2^4 +2^0')
    ,(5, 7   , '= 4+2+1   = 2^2 + 2^1 +2^0')   
) t(id, pow, comment)
where pow % power(2, @p+1) >= power(2, @p)
user_62199790
Related