I have an old database design I'm working with that uses bitmasks. I have table that houses email addresses and a bitmask based on another table, and have been tasked with writing a SQL query which "breaks down" these bit masks. Generally, this data is viewed in a different manner, where we utilize a bitwise & to determine if the bitmask is valid, but not the case this time...
In a simplified version of my data, I have 2 fields like so.
|---------------------|------------------|
| Email | bitMask |
|---------------------|------------------|
| test@test.com | 3 |
|---------------------|------------------|
| test2@test.com | 9 |
|---------------------|------------------|
The output I'm ultimately looking for would show what "powers of 2", or what "bits" it took to get to the bitmask, so I can cross-reference the mask values in another table. So my sample output is something like this:
|---------------------|------------------|
| Email | value |
|---------------------|------------------|
| test@test.com | 2 |
|---------------------|------------------|
| test@test.com | 1 |
|---------------------|------------------|
| test2@test.com | 8 |
|---------------------|------------------|
| test2@test.com | 1 |
|---------------------|------------------|
This is the opposite way I'm used to dealing with bitmasks. If anyone knows how to go about writing this query, any advice is appreciated.
Use & (bitwise AND operator), with 0xFFFFFFFE to get the powers of 2 part and with 1 to get the 1st binary digit (0 or 1):
select [Email], [bitMask] & 0xFFFFFFFE [value]
from tablename
union all
select [Email], [bitMask] & 1
from tablename
order by [Email], [value] desc
See the demo.
Results:
> Email | value
> :------------- | ----:
> test@test.com | 2
> test@test.com | 1
> test2@test.com | 8
> test2@test.com | 1