Conditions

A condition specifies a combination of one or more expressions and logical (Boolean) operators. The OLAP expression syntax has the following types of conditions:

Simple Comparison
Group Comparison
Range
Multiple
Negation
Special
Pattern Matching

Simple Comparison Conditions

Comparison conditions compare one expression with another. The result of such a comparison can be TRUE, FALSE, or NULL.

You can use the following comparison operators:

Simple Comparison Operators
Operator	Description
`>`	Greater than
`>=`	Greater than or equal to
`<`	Less than
`<=`	Less than or equal to
`=`	Equal to
`!= ^=`	Not equal to
`<>`	Greater than or less than

Syntax

expr { > | >= | < | <= | = | != | ^= | <> } expr

Arguments

expr can be any expression.

Examples

5 > 3 is true, 4 != 5 is true, 6 >= 9 is false.

Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery.

You can use the following comparison operators:

Group Comparison Operators
Operator	Description
`>`	Greater than
`>=`	Greater than or equal to
`<`	Less than
`<=`	Less than or equal to
`=`	Equal to
`!= ^=`	Not equal to
`<>`	Greater than or less than
`ANY SOME`	Returns True if one or more values in the list match, or False if no values match.
`ALL`	Returns True if all values in the list match, or False if one or more values do not match.

Syntax

expr
     { = | != | ^= | <> | > | < | >= | <= }
     { ANY | SOME | ALL }
     ({ expression_list | subquery })

Examples

5 >= ALL (5, 10, 15) is true, 5 <> ANY (5, 10, 15) is true.

(color, size) = SOME (('RED', 'L'), ('GREEN', 'M')) returns true for large red and medium green items.

Range Conditions

The BETWEEN operator tests whether a value is in a specific range of values. It returns true if the value being tested is greater than or equal to a low value and less than or equal to a high value.

Syntax

expr [ NOT ] BETWEEN expr AND expr

Example

7 NOT BETWEEN 10 AND 15 is true.

Multiple Conditions

Conjunctions compare a single expression with two conditions.

Conjunctions
Operator	Description
`AND`	Returns `TRUE` if both component conditions are `TRUE`. Returns `FALSE` if either is `FALSE`. Otherwise returns `UNKNOWN`.
`OR`	Returns `TRUE` if either component condition is `TRUE`. Returns `FALSE` if both are `FALSE`. Otherwise returns `UNKNOWN`.

Syntax

expr operator condition1 { AND | OR } condition2

Example

5 < 7 AND 5 > 3 is true; 5 < 3 OR 10 < 15 is true.

Negation

The NOT operator reverses the meaning of a condition. It returns TRUE if the condition is FALSE. It returns FALSE if it is TRUE. If it is UNKNOWN, then it remains UNKNOWN.

Syntax

NOT {BETWEEN | IN | LIKE | NULL }

Example

5 IS NOT NULL is true; 5 NOT IN (5, 10, 15) is false.

Special Conditions

The IS operator test for special conditions, such as nulls, infinity and values that are not number.

Special Conditions Operators
Operator	Description
`IS INFINITE`	Returns `TRUE` if the expression is infinite, or `FALSE` otherwise.
`IS NAN`	Returns `TRUE` if the expression is not a number, or `FALSE` otherwise.
`IS NULL`	Returns `TRUE` if the expression is null, or `FALSE` otherwise.

Syntax

expr IS [ NOT ] NULL

Example

13 IS NOT NULL is true.

Pattern-Matching Conditions

The pattern-matching conditions compare character data. There are two groups of operators in this category: LIKE and REGEXP_LIKE.

LIKE Operators

The LIKE operators specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE operators can match patterns defined by special pattern-matching ("wildcard") characters.

You can choose from the following LIKE operators:

LIKE Pattern-Matching Operators
Operator	Description
`LIKE`	Uses characters in the input character set.
`LIKEC`	Uses Unicode complete characters. It treats a Unicode supplementary character as two characters.
`LIKE2`	Uses UCS2 code points. It treats a Unicode supplementary character as one character.
`LIKE4`	Uses UCS4 code points. It treats a composite character as one character.

Syntax

char1 [ NOT ] ( LIKE | LIKEC | LIKE2 | LIKE4 )
  char2 [ ESCAPE esc_char ]

Arguments

char1 is a text expression for the search value.

char2 is a text expression for the pattern. The pattern can contain these wildcard characters:

An underscore (_) matches exactly one character (as opposed to one byte in a multibyte character set) in the value.
A percent sign (%) can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. A '%' cannot match a null.

esc_char is a text expression, usually a literal, that is one character long. This escape character identifies an underscore or a percent sign in the pattern as literal characters instead of wildcard characters. You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @% to search for % and @@ to search for @.

Examples

'Ducks' LIKE 'Duck_' and 'Ducky' LIKE 'Duck_' are true.

'Duckling' LIKE 'Duck_' is false.

'Duckling' LIKE 'Duck%' is true.

REGEXP_LIKE Operator

REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This operator evaluates strings using characters as defined by the input character set.

Oracle regular expressions conform to the IEEE Portable Operating System Interface (POSIX) regular expression standard and to the Unicode Regular Expression Guidelines of the Unicode Consortium. For more information about regular expressions, refer to the Oracle Database SQL Reference.

Syntax

REGEXP_LIKE(source_char, pattern
            [, match_parameter ]
           )

Arguments

source_char is the expression that will be searched. It can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

pattern is the expression to search for. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes.

match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values:

c: Case-sensitive matching.
i: Case-insensitive matching.
m: Treat the source string as multiple lines. REGEXP_LIKE interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, REGEXP_LIKE treats the source string as a single line.
n: Newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x: Ignore whitespace characters.

Example

REGEXP_LIKE (first_name, '^Ste(v|ph)en$') matches Steven and Stephen, but not Stevens or Esteven, in first_name.