# Thread: HOWTO: Select BINARY(32) With Certain Bit Set?

1. Registered User
Join Date
Nov 2009
Posts
9

## Unanswered: HOWTO: Select BINARY(32) With Certain Bit Set?

Hello there people.

I'd like to select only records from the table below, where certain bit is set in the "Flags" column:

TABLE: USERS
Uid: BIGINT (User ID)
Name: NVARCHAR(64)
Flags: BINARY(32)

"Flags" column contains bits that carry various information. For example, a banned user will have its 4th bit turned-on (0x00000008). If I were to get all banned users I will need to get all records where "(Flags & 0x00000008) != 0". Can this be done at the SQL level? Sorry if that sounds inexperienced, I'm more of a C++ guy

Ben.

2. :-)
Join Date
Jun 2003
Location
Posts
5,516
You'll be surprised, but that's how you can do it:
Code:
`... where flags & 8 != 0`
If you must use hexadecimal notation, convert it to an integer, because bitwise operators require integers on the right side:
Code:
`... where flags & convert(int, 0x08) != 0`

3. Registered User
Join Date
Nov 2009
Posts
9
Thanks a lot, Nick! It helps a great deal!

One other thing, if the "Flags" column is BINARY(8), do you mean the 8-bit column gets promoted to integer before the bitwise AND operation? What if "Flags" column is BINARY(80)? Then I think that goes out-of-bound for an integer right?

Thanks again,
Ben.

4. :-)
Join Date
Jun 2003
Location
Posts
5,516
Originally Posted by Ben Goh
One other thing, if the "Flags" column is BINARY(8), do you mean the 8-bit column gets promoted to integer before the bitwise AND operation? What if "Flags" column is BINARY(80)? Then I think that goes out-of-bound for an integer right?

You'll have to try it yourself, but my guess would be that internally bitwise operations require integer types, which would limit the scale to bigint (64 bits).

5. Registered User
Join Date
Nov 2009
Posts
9
Thanks again Nick. I'm not that far off into implementation just yet, I'll try it out later myself when I'm there.

Cheers,
Ben.