Is-Of-Type Operator

Syntax

is_of_type_expression ::= 
   add_expression IS [NOT] OF [TYPE] 
   "(" [ONLY] sequence_type ([ONLY] sequence_type)* ")"

Semantics

The is-of-type operator checks the sequence type of its input sequence against one or more target sequence types. If the number N of the target types is greater than one, the expression is equivalent to OR-ing N is-of-type expressions, each having one target type. So, for the remainder of this section, we will assume that only one target type is specified.

The is-type-of operator will return true if both of the following conditions are true:

1. the cardinality of the input sequence matches the quantifier of the target type. Specifically,
   1. if the quantifier is * the sequence may have any number of items,
   2. if the quantifier is + the input sequence must have at least one item,
   3. if the quantifier is ? The input sequence must have at most one item, and
   4. if there is no quantifier, the input sequence must have exactly one item.
2. all the items in the input sequence are instances of the target item-type (type_def), i.e. the type of each input item must be a subtype of the target item-type. For the purposes of this check, a NULL is not considered to be an instance of any type.

If condition (1) is satisfied and the input sequence contains a NULL, the result of the is-type-of operator will be NULL. In all other cases, the result is false.

Example 6-50 Is-Of-Type Operator

Find all the users whose address information has been stored as a single, flat string.

SELECT id
FROM users u
WHERE u.address IS OF TYPE (STRING);