Constraint Syntax |
|
The following table lists comparators that can be used with the VALUE key word to create valid table, column, domain, or replication constraints. These comparators may also be used to create WHERE conditions for filtering data or creating views, or WHEN conditions for creating triggers.
You can combine simple conditions with the logical operators AND, OR, and NOT to form compound conditions. You can use the keyword AND to combine two search conditions that must both be true. You can use the keyword OR to combine two search conditions when one or the other (or both) must be true. Finally, you can use the keyword NOT to select rows where a search condition is false
|
Comparator |
Description |
|
Relational Operators |
These may be one of the following: >, >=, <=, <, =, and <>. The relational operator condition is satisfied when the expression on either side of the relational operator fulfills the relation set up by the operator. |
|
BETWEEN |
This comparison takes the form: x BETWEEN y AND z. The BETWEEN condition is satisfied when the value or expression to the left of the BETWEEN keyword lies in the range (denoted by the AND keyword) of the two expressions on the right of the keyword. |
|
IN |
This comparison takes the form: x IN (y, z, ...). The IN condition is satisfied when the value or expression to the left of the IN keyword is included in the list of values to the right of the keyword. |
|
IS NULL |
This takes the form: x IS NULL. The IS NULL condition is satisfied when the value or expression to the left of the IS NULL keywords is a NULL value. |
|
IS NOT NULL |
This takes the form: x IS NOT NULL. The IS NOT NULL condition is satisfied when the value or expression to the left of the IS NOT NULL keywords contains a value other than a NULL value. |
|
LIKE |
This takes the form: x LIKE 'y' ESCAPE 'z'. The LIKE condition is satisfied when the string value or expression to the left of the LIKE keyword meets the criteria specified in the case-sensitive quoted string to the right of the keyword. You can use the percent symbol (%) and the underscore symbol (_) as wildcards in the quoted strings. The percent symbol matches zero or more characters, and the underscore symbol matches exactly one character. The ESCAPE clause is optional and allows you to define an escape character so you can include the percent and underscore symbols in the quoted string without having them interpreted as wildcards. To include a single-quote character in a quoted string, use two consecutive single-quotes. |
|
MATCH |
This takes the form: x NOT CASE MATCH 'y'. The MATCH condition is satisfied when the quoted string to the right of the MATCH keyword matches the entire string value or expression to the left of the keyword. The NOT and CASE keywords are optional. The NOT keyword inverts the search results, and the CASE keyword makes the search case-sensitive. |
|
CONTAIN |
This takes the form: x NOT CASE CONTAIN 'y'. The CONTAIN condition is satisfied when the quoted string to the right of the CONTAIN keyword matches any part of the string value or expression to the left of the keyword. The NOT and CASE keywords are optional. The NOT keyword inverts the search results, and the CASE keyword makes the search case-sensitive. |
Table: Constraint Syntax Comparators
Related topic: