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

Get "need me too" and "alpha" together

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

Get "need me too" and "beta" together

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


Tweet