Comparison Operators
Comparison operators are used to compare the equivalency or inequivalency of 2
expressions of the same field type. The result of a comparison expression is a Boolean
value (returns true, false, or NULL for invalid, such as comparing a text value to a
numeric value). Boolean expressions are most often used to specify data processing
conditions or filter criteria.
Example: You can use comparison operators in a
CASE
expression:CASE WHEN [age] <= 25 THEN "0-25" WHEN [age] <= 50 THEN "26-50" ELSE "over 50" END
This expression compares the value in the age field to a literal number value. If true,
it returns the appropriate boolean value.
Operator | Meaning | Example |
|---|---|---|
= or ==
| Equal to | [order_date] = "12/22/2016"
|
>
| Greater than | [age] > 18
|
!>
| Not greater than (equivalent to < ) | [age] !> 8
|
<
| Less than | [age] < 30
|
!<
| Not less than (equivalent to >= ) | [age] !< 12
|
>=
| Greater than or equal to | [age] >= 20
|
<=
| Less than or equal to | [age] <= 29
|
<> or != or ^=
| Not equal to | [age] <> 30
|
BETWEEN
min_value AND max_value
| Test whether a date or numeric value is within the min and max values (inclusive). | [year] BETWEEN 2014 AND 2016
|
IN(
list ) | Test whether a value is within a set. | [product_type] IN("tablet","phone","laptop")
|
LIKE("
pattern ") | Simple inclusive case-insensitive character pattern matching. The
* character matches any number of characters. The
? character matches exactly 1 (a single) character. | [last_name] LIKE("?utch*")
Matches Kutcher , hutch but not Krutcher or
crutch [company_name] LIKE("workday") Matches Workday or workday |
value
IS NULL
| Check whether a field value or expression is null (empty). | [ship_date] IS NULL
Evaluates to true when the ship_date field is empty |