Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    9

    Smile 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

    Thanks in advance,
    Ben.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    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. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Ben Goh View Post
    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).
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •