Oracle in a Nutshell

Other Conditions

Oracle also allows you to specify a number of additional conditions:

Range condition

Returns a value based on whether expr falls between two other values. The syntax is:

                           expr [NOT] BETWEEN comparison_expr1 AND comparison_expr2

NULL

Returns a value based on whether the expression is NULL or NOT NULL. The syntax is:

                           expr IS [NOT] NULL

EXISTS

Returns TRUE if subquery returns at least one row. The syntax is:

EXISTS (subquery)

LIKE

Returns TRUE if char1 is in char2. Can use wildcards: underscore (_) to represent a single character or percent (%) for multiple characters. escape_char must be specified before the wildcard characters if these characters are to be treated as actual characters. The syntax is:

                           char1 [NOT] LIKE | LIKEC | LIKE2 | LIKE4 char2 [ESCAPE escape_char]

Note that for the comparison:

LIKEC uses Unicode complete characters.

LIKE2 uses UCS2 codepoints.

LIKE4 uses UCS4 codepoints.

EQUALS_PATH

Returns whether an XML document can be found at a specified path in the database. This type of condition is used with the views RESOURCE_VIEW and PATH_VIEW. It is new with Oracle9i Release 2. The syntax is:

EQUALS_PATH (column, path_string[, correlation_integer])

correlation_integer is used to correlate this condition with the DEPTH and PATH functions.

UNDER_PATH

Returns whether an XML document can be found under a specified path in the database. This type of condition is used with the views RESOURCE_VIEW and PATH_VIEW. It is new with Oracle9i Release 2. The syntax is:

UNDER_PATH (