Wiki - SQL Commands

[Kayot]

I'm putting this guide here so as to not lose it. This was a chat (http://www.eqemulator.net/forums/showthread.php?t=22593) involving samandhi (http://www.eqemulator.net/forums/member.php?u=26511), ndnet (http://www.eqemulator.net/forums/member.php?u=28051), and myself. The chat involved SQL syntax and bitmask, which till now bitmasks were elusive to me. Bitmask work on interesting terms.

ndnet wrote the bitmask part, I already understood the basic idea, but this model really helps.

Example -

Select * From table;
Desc - This puts up all the tables, let assume one bitmask field. In this example there is only 5 entry's.


+------+
| mask |
+------+
| 5 | <--- 0101 = 0 + 4 + 0 + 1 = 5
| 2 | <--- 0010 = 0 + 0 + 2 + 0 = 2
| 3 | <--- 0011 = 0 + 0 + 2 + 1 = 3
| 8 | <--- 1000 = 8 + 0 + 0 + 0 = 8
| 4 | <--- 0100 = 0 + 4 + 0 + 0 = 4
+------+


Goal - I want every line where the number 4 (or 0100) is turned on, this would include values 4 and five 5 because 4 = 0100, and 5 = 0101. Both have the number 4 bit turned on.

Select * From table where mask & 4;

Result:

+------+
| mask |
+------+
| 5 | <--- 0101 = 0 + 4 + 0 + 1 = 5
| 4 | <--- 0100 = 0 + 4 + 0 + 0 = 4
+------+


But what if I don't want 4's? What if I want everything but 4's?

Select * from table where not mask & 4;


+------+
| mask |
+------+
| 2 | <--- 0010 = 0 + 0 + 2 + 0 = 2
| 3 | <--- 0011 = 0 + 0 + 2 + 1 = 3
| 8 | <--- 1000 = 8 + 0 + 0 + 0 = 8
+------+


And thats all about bitmaks, a list of operators;

BINARY Cast a string to a binary string
BIT_AND() Return bitwise and
BIT_COUNT() Return the number of bits that are set
BIT_LENGTH() Return length of argument in bits
BIT_OR() Return bitwise or
BIT_XOR() Return bitwise xor
& Bitwise AND
| Bitwise OR
^ Bitwise XOR
/ Division operator