| Modular Arithematic | mod(29,3) |
| Normal Math | 3+(2*4) |
| Binary OR | 27 | 31 |
| Binary AND | 27 & 31 |
| Binary LEFTSHIFT | 27 << 1 |
| Binary RIGHTSHIFT | 27 >> 1 |
| Binary INVERSION | ~4 |
| Count the set bits | BIT_COUNT(4) |
| Logical AND | age = 10 && age < 20 |
| Logical OR | age = 3 || age = 2 |
| Logical NOT | not age = 3 |
| Equality | = |
| Less than | < |
| Greater than | > |
| Less than or equal to | <= |
| Greater than or equal to | >= |
| Not equal to (version 1) | != |
| Not equal to (null is null) | <=> |
| Test for nullness | pop IS NULL, ISNULL(pop) |
| Test for non-nullness | pop IS NOT NULL |
| Range check | pop BETWEEN 3 AND 7 |
| Set inclusion | pop in (3, 4, 17) |
| Set exclusion | pop not in (3, 4, 17) |
| Interval checking | INTERVAL(23, 1, 10, 11, 20, 21, 30)
Intervals must be in order |
| Regular expression string functions
(SQL style) |
"David" LIKE "_avid"
"%" means any number of chars (including zero) _ means any one char |
| Relular expression string functions
(Unix style) |
David RLIKE ".u."
doesn't always work for me |
| If | IF(age > 10, "adult", "child") |
| Absolute value | ABS(pop - 1000) |
| Sign | SIGN(pop - 1000) |
| Rounding to closest int | ROUND(pop / 1000, 2) |
| A random number | RAND() |
| Leftmost part of a string | LEFT("Hello", 4)
right also works |
| Middle part of a string | MID("Hello", 3, 2)
starts counting at 1 |
| Sounds like | SOUNDEX("fil") = SOUNDEX("phil") |
If one or both arguments are NULL, the result of
the comparison is NULL, except for the <=> operator.
If both arguments in a comparison operation are
strings, they are compared as strings.
If both arguments are integers, they are compared
as integers.
Hexadecimal values are treated as binary strings
if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME
column and the other argument is a constant, the constant is converted
to a timestamp
before the comparison is performed. This is done
to be more ODBC-friendly.
In all other cases, the arguments are compared as
floating-point (real) numbers.