Operators

Case Insensitivity

Operator names are case-insensitive.

Logical Operators

Logical operators in precedence order: NOT, AND, OR.

Comparison Operators

Comparison operators: =, >, >=, <, <=, <> (not equal).

These operators can compare only values of comparable types. (Exact numeric values and approximate numerical values are comparable types.) Attempting to compare incomparable types yields false. If either value in a comparison evaluates to NULL, then the result is unknown (in SQL 3-valued logic).

Comparison of string values is restricted to = and <>. Two strings are equal if and only if they contain the same sequence of characters.

Comparison of boolean values is restricted to = and <>.

Arithmetic Operators

Arithmetic operators in precedence order:

  • +, - (unary)
  • *, / (multiplication and division)
  • +, - (addition and subtraction)

Arithmetic operations obey numeric promotion rules of the Java programming language.

Between Operator

arithmetic-expr1 [NOT] BETWEEN arithmetic-expr2 AND arithmetic-expr3

The BETWEEN comparison operator includes its endpoints. For example:
  • age BETWEEN 5 AND 9   is equivalent to   age >= 5 AND age <= 9
  • age NOT BETWEEN 5 AND 9   is equivalent to   age < 5 OR age > 9

String Set Membership

identifier [NOT] IN (string-literal1, string-literal2, ...)

The identifier must evaluate to either a string or NULL. If it is NULL, then the value of this expression is unknown. You can use a maximum of 32,767 string-literals in the string set.

Pattern Matching

identifier [NOT] LIKE pattern-value [ESCAPE escape-character]

The identifier must evaluate to a string.

The pattern-value is a string literal, in which some characters bear special meaning:

  • _ (underscore) can match any single character.
  • % (percent) can match any sequence of zero or more characters.
  • escape-character preceding either of the special characters changes them into ordinary characters (which match only themselves).

Null Header or Property

identifier IS NULL

This comparison operator tests whether a message header is null, or a message property is absent.

identifier IS NOT NULL

This comparison operator tests whether a message header or message property is non-null.