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

You should look into the "enum" and "set" types in MySQL (dev.mysql.com/doc/refman/5.7/en/set.html). — Gordon Linoff
– Gordon Linoff, Commented Mar 4, 2014 at 17:38

Marcus Adams · Accepted Answer · 2014-03-04 17:40:32Z

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

answered Mar 4, 2014 at 17:40

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcus Adams Over a year ago

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.

Pakk · Accepted Answer · 2014-03-04 18:06:07Z

1

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

answered Mar 4, 2014 at 18:06

Pakk

8592 gold badges19 silver badges39 bronze badges

Collectives™ on Stack Overflow

SQL Bitwise operation (MySql)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related