SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-16
Considerations—Character Expressions

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:
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 that was
defined with a COLLATE FRENCH clause, the collation FRENCH is implicitly
associated with the expression, although no COLLATE clause appears in the
expression.
A character expression is explicitly associated with a collation if the expression
itself includes the COLLATE clause. An explicit association with a collation
overrides an implicit association with a collation in the same expression.
COLLATE CHARACTER SET explicitly associates the binary ordering of
character-item values with the expression, overriding any implicit association
with a collation.
If a nested character expression includes more than one COLLATE clause, the
collation explicitly specified at the highest level of the expression is the collation
associated with the expression. For example, this expression is associated with the
collation SPANISH, even if B is a column associated with collation FRENCH:
MAX(B COLLATE FRENCH) COLLATE SPANISH
Collation FRENCH is used to compute the MAX value. The result has collation
SPANISH.

Determining the collating sequence for concatenated strings
The collating sequence of a concatenated string is determined by the rules
specified for a comparison operation. For more information, see Collations on
page C-43.