Bob Bob - 7 months ago 44
C# Question

Disassembling Bit Flag Enumerations in SQL Server

I have an

column in a SQL Server database which stores a value relating to a bit flag enumeration. For instance, if the enum is:

public enum UserType
StandardUser = 1,
Admin = 2,
SuperUser = 4

then the column in SQL Server might hold a value of 5.

What I need to do is select all of the rows from another table holding additional details about the
, so using the example of a value of 5, I would want to select the rows from the second table with IDs 1 and 4.

Does anyone know a clever way to break the number up in this manner - ideally the method should be recursive to some degree since this is a very simplified example, and the actual tables/enums are much bigger.

SELECT * FROM first_table f 
  JOIN second_table s ON s.ID & f.Flags <> 0 
  WHERE f.something = something

This would select all rows from second_table that matches any of the flags on the given row in the first table.