CODEWITHSUNDEEP
sqlsql-serverbit-manipulation
Alberto de Vargas
Alberto de Vargas

Reputation: 15

Bitwise query multiples values

I want execute a query with bitwise in SQL Server.

I have a Event table with values with all combinations.

If Monday is (2^0) 1, and Sunday is (2^6) 64, I want all records with monday, wednesday and sunday, I write this query:

SELECT Distinct(DayBitwise)
FROM Assets
WHERE DayBitwise | 1 | 4 | 64 = 1 | 4 | 64
ORDER BY DayBitwise

Expected result: 

1, 3, 4, 5, 6, 7, 9, 11, 12, 13, 15, 17, 19, 20, 21, 23, 25, 27, 28, 29, 31 ....

Actual result: 1,4 ,5, 64, 65, 68

Thanks.

Upvotes: 0

Views: 158

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you want to see if all three days are present, then do:

WHERE (DayBitwise & 1 & 4 & 64) = (1 | 4 | 64)

If you want any, then:

WHERE (DayBitwise & (1 | 4 | 64)) > 0

Note: I don't recommend using bit columns for this purpose. Bit operations are not particularly efficient in SQL.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272086

Since you're interested in checking if any of the monday, wednesday or sunday bit is set you need to use AND mask and that the result is non-zero:

WITH assets(DayBitwise) AS (
    SELECT number
    FROM master..spt_values
    WHERE type = 'p' AND number BETWEEN 0 AND 127
)
SELECT *
FROM assets
WHERE DayBitwise & (1 | 4 | 64) <> 0

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

If everything else is fine then probably this would be your answer

    go

    SELECT Distinct DayBitwise FROM Assets 
    WHERE DayBitwise | 1 | 4 | 64 = 1 | 4 | 64
    ORDER BY DayBitwise

     go

If it is not working then kindly share the error message.

Upvotes: 0

Related Questions