Relational Expressions

All relational expressions involving at least one expression of unichar or univarchar, are based on the default Unicode sort order. If one expression is unichar and the other is varchar (nvarchar, char, or nchar), the latter is implicitly converted to unichar.

The following table shows which expressions are most often used in where clauses, and where they may be combined with logical operators.

When comparing Unicode character data, “less than” means closer to the beginning of the default Unicode sort order, and “greater than” means closer to the end. “Equality” means the Unicode default sort order makes no distinction between two values (although they need not be identical). For example, the precomposed character ê must be considered equal to the combining sequence consisting of the letter e followed by U+0302. (A precomposed character is a Unicode character you can decompose into an equivalent string of several other characters.) If the Unicode normalization feature is turned on (the default), Unicode data is automatically normalized and the server never sees unnormalized data.

expr1 op_compare [any | all] (subquery)

The use of any or all with comparison operators and subquery expr2 , implicitly invokes min or max. For instance, “expr1> any expr2” means, in effect, “expr1> min(expr2)”.

expr1 [not] in (expression list)

expr1 [not] in (subquery)

The in operator checks for equality with each of the elements in expr2, which can be a list of constants, or the results of a subquery.

expr1 [not] between expr2 and expr3

The between operator specifies a range. It is, in effect, shorthand for “expr1 = expr2 and expr1<= expr3”.

expr1 [not] like "match_string" [escape"esc_char”]

The like operator specifies a pattern to be matched. The semantics for pattern matching with Unicode data are the same as for regular character data. If expr1 is a unichar column name, then “match_string” may be either a unichar string or a varchar string. In the latter case, an implicit conversion takes place between varchar and unichar