Map bitwise enumeration on sql column value

Question

I have a bitwise enumeration with FlagsAttribute installed above it, like this -

[FlagsAttribute] public enum MyEnum { None = 0, First = 1, Second = 2, Third = 4, Five = 8, Six = 16, Seven = 32, Eight = 64, Nine = 128 } 

Now, in C #, I save this value in the say property MyProperty, and when I save it, I write this property to my SQL database in the integer column. Suppose that if I selected First,Second,Five from the code, then in the database it will be saved as '11' .

I know that I can extract the value from the database and just need to specify the int value for MyEnum, and this will give me the values. But I want some manipulations to be performed on SQL data in some stored procedure, where, obviously, I cannot cast it to an Enum value. So, is there a way out that can tell me about the individual values.

As in the example, if 11 is stored, in any way that I can get as "1+2+8"

Answer 1

This may help you get started:

 Select 11 & 1 As 'First' , 11 & 2 As 'Second' , 11 & 4 As 'Third' , 11 & 8 As 'Five' , 11 & 16 As 'Six' , 11 & 32 As 'Seven' , 11 & 64 As 'Eight' , 11 & 128 As 'Nine'; 

Where 11 is your stored value.

This will return non-zero values ​​for each set value (i.e. Select 11 & 1 As 'First' returns 1 , Select 11 & 2 As 'Second' returns 2, Select 11 & 4 As 'Third' returns 0 , etc.

Answer 2

You can do bitwise operations in SQL

 Select * From MyTable Where MyEnum = (1 | 2 | 8) 

Checkbox return

 Select Case when (MyEnum & 1) = 1 Then 1 else 0 End as First, Case when (MyEnum & 2) = 2 Then 1 else 0 End as Second, Case when (MyEnum & 4) = 4 Then 1 else 0 End as Third, Case when (MyEnum & 8) = 8 Then 1 else 0 End as Fourth, Case when (MyEnum & 16) = 16 Then 1 else 0 End as Fifth From MyTable 

Answer 3

 declare @MyEnum as Int set @MyEnum = 11 select case when ( @MyEnum & 1 ) = 1 then '1+' else '' end + case when ( @MyEnum & 2 ) = 2 then '2+' else '' end + case when ( @MyEnum & 4 ) = 4 then '4+' else '' end + case when ( @MyEnum & 8 ) = 8 then '8+' else '' end as Bitses 

(possibly) trailing plus is left as an exercise for the reader.

Answer 4

 ID Name 1 Zero 2 One 4 Two 8 Three 16 Four 32 Five 

A value of 26 (or 11010) corresponds to One + Three + Four. To get a description for this, you can use the following query

 DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ' | ', '') + Name FROM [Play].[dbo].[Enums] where (26 & ID)=ID Select @Names; 

He will give you the following result One | Three | Four One | Three | Four

if you want to get only identifiers

 DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + STR(ID) FROM [Play].[dbo].[Enums] where (26 & ID)=ID Select @Names;