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:
- the cardinality of the input sequence matches the quantifier of the target type. Specifically,
- if the quantifier is * the sequence may have any number of items,
- if the quantifier is + the input sequence must have at least one item,
- if the quantifier is ? The input sequence must have at most one item, and
- if there is no quantifier, the input sequence must have exactly one item.
- 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);