NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-13
Considerations—Character Expressions
Considerations—Character Expressions
•
Guidelines for using character expressions in SQL statements
Character expressions can appear at any place where a string literal, parameter,
column, or host variable can appear. The following diagram lists the usage of
character expressions:
{ character-expression relat-operator character-expression}
{ }
{ character-expression [NOT] LIKE character-expression }
{ }
{ character-expression [NOT] BETWEEN }
{ character-expression AND character-expression }
{ }
{ character-expression IS [NOT] NULL }
{ }
{ character-expression [NOT] IN { subquery } }
{ { in-value-list } }
{ }
{ character-expression relat-operator [ALL ] subquery }
{ [ANY ] }
{ [SOME] }
{ }
{ UPSHIFT ( character-expression ) }
When evaluating predicates with character expressions, the rules that apply to string
literals, columns, and other forms of character expressions also apply to this usage.
•
Guidelines for using the concatenation operator
The sum of lengths of the character string operands cannot exceed the maximum
allowed length for their data type. If their length exceeds the maximum allowed for
a character data type, SQL truncates the string to the right and issues a warning.
Strings with different character sets cannot be concatenated together.
If either of the two character string operands is a null value, the result is a null value.
If a concatenated expression is used in a comparison (as in a WHERE clause), the
collation of the resulting expression must not be undefined. If the concatenation is
not used in a comparison (as in a select list that is not ordered), it can have an
undefined collation.
•
Associating collations with character data
To associate a collation with a parameter of a character data type, use the COLLATE
command, as follows:
character-item [ COLLATE { collation } ]
{ CHARACTER SET }
A character expression is implicitly associated with a collation if character-
item is defined with a collation. For example, if character-item is a column