# How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against?

How can I convert the final integer of a bitwise OR back to it's original set of integers that the bitwise OR operation was applied to?

For example if I have the following set of bit values: {0, 1, 2, 4, 8, 16} and if I have the bitwise OR generated value of 11 then I would like to convert that value of 11 back to 1, 2, and 8 (the only possible combinations of values).

• Could you provide an example? It is not a 1->1 operation, so there may be a lot of possible combinations. – Piotr Nov 26 '19 at 20:25

The answer provided by J.D. works, except in the example the value 1 is not included in the results, even though for 11 the correct bits are 1, 2, 8.

The below example extends the logic from that answer, but uses a recursive CTE instead of a loop to generate the bit values and wraps it in a function that can be called easily to return a table of bits for a given mask. It also doesn't use a table to store the bit values and generates them on the fly.

Code:

``````CREATE FUNCTION fnGetBitsForMask (@Mask INT)
RETURNS TABLE
AS
RETURN
WITH BitwiseValues AS
(
SELECT CAST(1 AS INT) AS RowNumber, CAST(0 AS INT)  AS BitValue
UNION ALL
SELECT b1.RowNumber+1 AS RowNumber, POWER(2, b1.RowNumber-1) AS BitValue
FROM BitwiseValues b1
WHERE b1.RowNumber < 30
)
SELECT b.BitValue
FROM BitwiseValues b
WHERE b.BitValue & m.Mask > 0
``````

In use:

``````SELECT * FROM dbo.fnGetBitsForMask(11)
``````

Result:

``````BitValue
1
2
8
``````

As a Table-Valued Function, you can use this in SELECT statements and other queries without having to maintain tables or use loops.

• Implementing this as a TVF is very neat. However, there are a couple of improvements I can see that could be made. Firstly, the row generated by the anchor (`SELECT CAST(1 AS INT) AS RowNumber, CAST(0 AS INT) AS BitValue`) is completely superfluous, and secondly, starting with the row number of 1 and capping the output at the row number of 30 limits the max value to just 268,435,456. This way there's also no correlation between `RowNumber` and `BitValue`, though I can see that the solution doesn't require it. – Andriy M Nov 27 '19 at 7:22
• Still, I think the rCTE output would make slightly more sense if it started with `(0, 1)` and ended with `(30, 1073741824)`. You could do it without the `POWER` function too. One other peculiarity is your usage of `CROSS JOIN`. I'm not sure if it makes it better in any way, I'm just curious why you decided to go with using the parameter via a cross join instead of using it directly in the `WHERE` clause, i.e. `b.BitValue & @Mask > 0`. – Andriy M Nov 27 '19 at 7:23
• Obligatory fiddle and slightly more fun fiddle – Peter Vandivier Nov 27 '19 at 9:09
• @AndriyM I use the RowNumber value from the anchor as the input to the POWER function because that was how I got it to work, tbh I didn't spend a lot of time optimising or thinking of better ways to do it, this is just a simple example of something that works. – HandyD Nov 27 '19 at 22:24
• @PeterVandivier Great catch, I didn't think to put it in a fiddle to demo the function. P.S. I like the more fun fiddle best :) – HandyD Nov 27 '19 at 22:26

I believe I figured out the answer. The quick version is, to convert from a bitwise OR generated value, I would need to apply a bitwise AND to between each individual bit value and the bitwise OR generated value.

The way I did this was I generated a table of all possible bitwise values in my set (I actually just made a table with the first 30 bitwise values using the SQL POWER() function, because after 30 you run into arithmetic overflow) and then cross joined that table to my bitwise OR generated value and selected the bitwise AND operation between the bitwise values and my bitwise OR generated value, then filtered out where the result is 0.

SQL to create bitwise values table:

``````DECLARE @LoopCounter AS INT = 0

WHILE (@LoopCounter < 30)
BEGIN

SET @LoopCounter = @LoopCounter + 1

SELECT @LoopCounter AS RowNumber, POWER(2, @LoopCounter) AS BitValue

END
``````

SQL to join to my bitwise OR generated value and get my results after:

``````SELECT 11 AS BValue