Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Question Unanswered: where field & 123=123

    What does this where statement mean?
    where field & 123=123

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In most cases, it means that the code was written by someone that shouldn't be writing SQL

    The & operator performs a Boolean "bitwise and" operation and checks the result of that operation against a constant. In effect, it checks to be certain that the low order seven bits of the field are 1111011.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    4
    PatP,
    One of our IT guys gives this to me and I (not in IT). He gave me this to extract some data I require for some analysis. I am trying to understand what is means, without going back to him ;-).
    Would it make more sense if the exact where clause was where status & 524288 = 524288.
    Also how do you get to 1111011. I tried converting 123 from ASCII into Bits, which gives me 001100010011001000110011.
    What does lower order mean? Last?
    Gretel

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's an old IT joke that goes: There are 10 kinds of people in the world: those who understand binary and those who don't.

    The crux of that joke is that 10 in binary is 2 in decimal. If you don't understand binary and interpret the 10 in decimal, you'll never get the joke.

    I'll break down this analysis in steps. You probably don't need to understand all of these steps, but I'll include them so that you can figure out which parts are important and which are fluff for your case.
    1. 524288 in decimal is 80000 in hexadecimal or 1000000000000000000 in binary.
    2. The status & 524288 part of the expression does something called a "bit mask", meaning that it only returns the bits set in the result where that bit was set in both the status and the constant.
    3. Comparing that result for equality against the bitmap checks to see if the interesting bit was "on" in the status.

    At this point, the IT guy has given you a SQL statement that will fish out a given bit from the binary representation stored in the status column, and only return the rows where that bit was "on" or set.

    By convention, the lower order (smaller or rightmost) bits are the least serious. As you move toward higher order (bigger numbers, further left) the problems are usually more serious. If this status column follows convention, you're looking for something quite "interesting" with this query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2013
    Posts
    4
    PatP,
    It now makes sense.
    I converted all the values in the status field from decimal to binary and the 20th bit from the right is indeed always on in the records that came back.
    Thanks a "11110100001001000000" !
    Gretel

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're most welcome!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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