Bob Bob - 3 months ago 19
C# Question

Disassembling Bit Flag Enumerations in SQL Server

I have an

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

[Flags()]
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
UserType
, 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.

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

Comments