Operators and Syntax Delimiters
The supported operators and syntax delimiters in the MI SQL Reference are outlined below. Note in the Boolean operator section, the links for each operator go to the rules and examples for delegating operators to the data provider, which will increase processing performance. Boolean operators in a query can be delegated to the data provider provided they follow the correct form. The rules for delegation is located in the Resources and Data section.
| Boolean Operators | Definition |
|---|---|
| Attribute operators |
=, < >, !=, <, <=, >, >= |
| Between | Returns true if numeric or date values fall within a range. Between is an inclusive operator. |
| EnvelopesIntersect | Returns true if the envelopes (MBRs) of the operands intersect. |
| Contains | Returns true if the first object contains all of the second object |
| Within | Returns true if the first object is entirely inside the second object |
| ContainsCentroid | Returns true if the first object contains the centroid of the second object |
| CentroidWithin | Returns true if the first object's centroid is within the second object |
| Intersects | Returns true if the two objects intersect at some point, or if part of the first object is within the second object, or if the first object contains part of the second object |
| In (List) | Returns true if equals at least one of the values in the literal list or sub query |
| Like | Returns true if the value can be compared to similar values using wildcard characters. There are two wildcards used in conjunction with the Like operator; Underscore "_" and Percent "%". The underscore represents a single number or character. The percent sign represents zero, one, or multiple characters. The symbols can be used in combination. |
| AND | Returns true if both conditions in the WHERE clause are true. |
| OR | Returns true if either the first or second condition is true. |
| NOT | Reverses the meaning of the logical operator with which it is used. |
| Arithmetic Operators | Definition |
|---|---|
| + | Addition, also concatenation operator. NOTE: MapBasic uses "&" |
| - | Subtraction |
| * | Multiplication |
| / | Division |
| ^ | Exponentiation |
Note: Operator math on Time or DateTime is not supported. You can add a number to a Date,
but not to a Time or DateTime.
| Syntax Delimiters | Definition |
|---|---|
| ( ) | Expression delimiters |
| " " | String constant delimiters. See Quote Rules. |
| "" "" | Quoted identifier delimiters |
| %, _ | Wildcard symbols. % represents zero, one or more characters; the_ (underscore) represents a single character. |
| , | List items and function argument separators |
| @, : | Parameter names |