Bitmasks in SQL

Mark Sta Ana - Aug 26 '18 - - Dev Community

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