3.2. Operators¶
3.2.1. Arithmetic¶
Addition:
a + b
Subtraction:
a - b
Multiplication:
a * b
Division:
a / b
Modulo (remainder):
a % b
Power:
a ^ b
a
and b
must be null
or number
.
3.2.2. Equality¶
Equality:
a = b
For inequality:
a != b
a <> b
a
and b
must be the same type, and accepted types are:
null
number
string
array
object
In the case of an array
both arrays must be the same length and contain
exactly the same element in the same order:
SELECT [1, 2] = [1, 2]
true
SELECT [1, 2] = [1, "2"]
false
SELECT [1, 2] = [2, 1]
false
In the case of an object
both objects must contain the same amount of keys
and each key must exist in both objects with the exact same value.
SELECT {"foo": 123} = {"foo": 123}
true
SELECT {"foo": 123} = {"foo": 123, "bar": null}
false
Inequality has all the same rules but in reverse.
3.2.3. Greater or Less Than¶
Greater than:
a > b
Greater than or equal to:
a >= b
Less than:
a < b
Less than or equal to:
a <= b
a
and b
must be the same type, and accepted types are:
null
number
string
When comparing strings it follows the same rules as how Lua compares strings.
3.2.4. Concatenation¶
Concatenation:
a || b
Will concatenate the string representations of both sides. For example
3 || 5
is 35
. Special values will be converted as follows:
Value | String Representation |
---|---|
null |
"" |
true |
"true" |
false |
"false" |
You cannot concatenate arrays or objects on either or both sides.
3.2.5. Logical¶
For all logical operations a
and b
are only allowed to be null
or
boolean
.
Logical AND:
a AND b
Results:
AND | true |
false |
---|---|---|
true |
true |
false |
false |
false |
false |
Logical OR:
a OR b
Results:
OR | true |
false |
---|---|---|
true |
true |
true |
false |
true |
false |
3.2.6. Regular Expressions¶
Regular Expressions:
value LIKE regex
value NOT LIKE regex
value
must be a string, but can be of any length.
regex
uses the SQL rules for LIKE
expressions.
Character | Description |
---|---|
. |
Match any single character. |
% |
Match zero or more characters. |
3.2.6.1. Examples¶
Test if a string starts with another string:
SELECT "Bob Smith" LIKE "Bob %"
Test if a string ends with another string:
SELECT "Bob Smith" LIKE "% Smith"
3.2.7. Checking Types¶
The following can be used to test the types of a value:
value IS null
value IS true
value IS false
value IS boolean
value IS number
value IS string
value IS array
value IS object
Each of the combinations can be used with NOT
like:
value IS NOT boolean
The case of the type (boolean
) is not important and there is no specific
convention on case.
3.2.8. Set Membership¶
To test the existence of a value in a set:
a IN (b1, b2, ...)
a NOT IN (b1, b2, ...)
Will return true
if a
exists in one of the b
values. There must be
at least one b
value. Comparison of each element follows the same rules as
the =
operator.
If a
is null
or any of the b
values are null
then the result is
null
. This is to conform is the SQL standard in dealing with null
values.
3.2.9. Containment¶
To test if a value sits between two other values (inclusive):
a BETWEEN b AND c
a NOT BETWEEN b AND c
Is exactly equivalent to:
a >= b AND a <= c
a < b OR a > c
If at least one of a
, b
or c
is null
then the result will always
be null
.
3.2.10. Operator Precedence¶
Operator/Element | Associativity | Description |
---|---|---|
- |
right | unary minus |
^ |
left | exponentiation |
* / % |
left | multiplication, division, modulo |
+ - |
left | addition, subtraction |
IS |
test for true , false , null |
|
IN |
set membership | |
BETWEEN |
containment | |
LIKE ILIKE |
string pattern matching | |
< > |
less than, greater than | |
= |
right | equality, assignment |
NOT |
right | logical negation |
AND |
left | logical conjunction |
OR |
left | logical disjunction |