SQL Bitwise operation (MySql)

Question

So in my table i have id and bitwise columns like so

+----+---------+
| id | bitwise |
+----+---------+
| 1 | 1 |
| 2 | 6 |
| 4 | 60 |
+----+---------+

From my c# code i'm setting these names to these binary values

Name1 = 0x0001,
Name2 = 0x0002,
Name3 = 0x0004,
Name4 = 0x0008,
Name5 = 0x0010,
Name6 = 0x0020,
Name7 = 0x0040,
Name8 = 0x0080,
Name9 = 0x0100,
Name10 = 0x0200,
Name11 = 0x0400,
Name12 = 0x0800

I would like to know the best way to extract the specific record for example lets use id=2 and its representing value

id=2 should yield Name2, Name3

At the moment I'm not sure how I can select a bit from the table into the values without a Huge If Else and case when Statement.

I'm not asking for you to solve this (unless you want to) I just need to know the next steps like how to create a list and or if this list needs to reside in a table format

• Gordon Linoff: You should look into the "enum" and "set" types in MySQL (dev.mysql.com/doc/refman/5.7/en/set.html).

Answer 1

If those Name values were in a table, you could use a JOIN:

SELECT t2.name
FROM table1 t1
JOIN table2 t2
ON t1.bitwise | t2.value = t1.bitwise
WHERE t1.id = 2

• Marcus Adams: See how I started my answer with "if". It's a proposed solution. If you can refactor to enums and sets, you can refactor by adding a table.

Answer 2

You could just write out that if statement, like so

DECLARE @BitWiseResult varchar(max)
Select @BitWiseResult = CASE WHEN SUBSTRING(CAST(bitwise as varchar(8)),len(CAST(bitwise as varchar(8))) -1 , len(CAST(bitwise as varchar(8)))) like '1' THEN 'Name1, ' END 
 + CASE WHEN SUBSTRING(CAST(bitwise as varchar(8)),len(CAST(bitwise as varchar(8))) -1 , len(CAST(bitwise as varchar(8)))) like '2' THEN 'Name2, ' END
 + CASE WHEN SUBSTRING(CAST(bitwise as varchar(8)),len(CAST(bitwise as varchar(8))) -1 , len(CAST(bitwise as varchar(8)))) like '4' THEN 'Name3, ' END
...
....
..... 
FROM TableWithBitwiseNumber
WHERE id = 2