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

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


CREATE FUNCTION fnGetBitsForMask (@Mask INT)
    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
    CROSS JOIN (SELECT @Mask AS Mask) m
    WHERE b.BitValue & m.Mask > 0

In use:

SELECT * FROM dbo.fnGetBitsForMask(11)



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

  • 1
    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
  • 1
    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
  • 1
    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)

    SET @LoopCounter = @LoopCounter + 1

    INSERT INTO BitmaskValues
    SELECT @LoopCounter AS RowNumber, POWER(2, @LoopCounter) AS BitValue


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

INTO #Mask;

WITH BitwiseOperation AS
    SELECT BV.BitValue, M.BValue, BV.BitValue & M.BValue AS BitwiseAnd
    FROM BitmaskValue AS BV

SELECT BitValue, BValue, BitwiseAnd
FROM BitwiseOperation
WHERE BitwiseAnd <> 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.