DEV Community

Mark Sta Ana
Mark Sta Ana

Posted on • Originally published at booyaa.wtf on

Bitmasks in SQL

bitmasks are really handy way to express predicates without becoming overly verbose with parens and logical operators (AND and OR). Assume we have the following table.

| what        | wanted |
|-------------|--------|
| need me too | 4      |
| alpha       | 1      |
| beta        | 2      |

with data as
(
select 'appears_in_both', 4 as wanted from dual
union all
select 'alpha', 1 as wanted from dual
union all
select 'beta', 2 as wanted from dual
)
...
Enter fullscreen mode Exit fullscreen mode

Get "need me too" and "alpha" together

...
select * 
from data
where bitand(wanted, 5) <> 0; -- (4 + 1)
Enter fullscreen mode Exit fullscreen mode

Get "need me too" and "beta" together

...
select * 
from data
where bitand(wanted, 6) <> 0; -- (4 + 2)
Enter fullscreen mode Exit fullscreen mode

Discussion (0)