Character Strings and Collations

[Obscure Rule] applies for this entire section.

A Collation, or collating sequence, is a set of rules that determines the result when character strings are compared. The result of any character string comparison thus depends on the Collation used -- we'll call this the relevant Collation. Different Collations might result in different comparison results for the same two strings, e.g.: a case-sensitive Collation will determine that the letter "A" and the letter "a" are not equal, but a case-insensitive Collation will determine that "A" and "a" are equal.

Whenever you're comparing an SQL character string, you may either specify the relevant Collation, or allow the comparison to be governed by a default Collation chosen by your DBMS. (To simplify matters, we recommend that you always follow the latter course. This will ensure that you get standard results across SQL-sessions.)

To explicitly specify a Collation for a comparison, add a COLLATE clause to your character string, as shown in the appropriate syntax diagrams in chapter. The Collation you name must either (a) be the default Collation for the relevant Character set or (b) be defined as a Collation for the relevant Character set by some CREATE COLLATION statement. If you're using COLLATE in an SQL-Schema statement, then the <AuthorizationID> that owns the containing Schema must have the USAGE Privilege on "<Collation name>". If you're using COLLATE in any other SQL statement, then your current <AuthorizationID> must have the USAGE Privilege on "<Collation name>".

If you choose not to specify a Collation for a comparison, the current default Collation is implicit. Your DBMS will choose the current default Collation for a character string using these rules:

First, to choose a character string's default Collation:

If a character string <data type> specification doesn't include a COLLATE clause but does include a CHARACTER SET clause, the default Collation for that <data type>'s values is the default Collation of the Character set named.
If any other character string value doesn't include a COLLATE clause, the default Collation for that value is the default Collation of the value's Character set.

Second, to choose one of the comparand's default Collations for the comparison:

Expressions that involve only non-Columns (i.e.: a <literal>, host language variable, parameter or expression result) are compared using the default Collation for the character string values' mutual Character set.
Expressions that involve both Columns (i.e.: <Column name>s or <Column reference>s) and non-Columns are compared using the Column(s)' mutual default Collation. If you want to compare values from multiple Columns with different default Collations, you must include a COLLATE clause in your expression.

Table 7.1

Collating Sequences and Coercibility Rules for Monadic Operations

OPERAND'S COERCIBILITY ATTRIBUTE	OPERAND'S COLLATION	RESULT'S COERCIBILITY ATTRIBUTE	RESULT'S COLLATION
Coercible	Default	Coercible	Default
Implicit	X	Implicit	X
No Collation	None	No Collation	None
Explicit	X	Explicit	X

Table 7.1 shows how the collating sequence and coercibility attribute are determined for the result of a monadic operation.

Table 7.2

Collating Sequences and Coercibility Rules for Dyadic Operations

OPERAND_1'S COERCIBILITY ATTRIBUTE	OPERAND_1'S COLLATION	OPERAND_2'S COERCIBILITY ATTRIBUTE	OPERAND_2'S COLLATION	RESULT'S COERCIBILITY ATTRIBUTE	RESULT'S COLLATION
Coercible	Default	Coercible	Default	Coercible	Default
Coercible	Default	Implicit	X	Implicit	X
Coercible	Default	No Collation	None	No Collation	None
Coercible	Default	Explicit	X	Explicit	X
Implicit	X	Coercible	Default	Implicit	X
Implicit	X	Implicit	X	Implicit	X
Implicit	X	Implicit	Y<>X	No Collation	None
Implicit	X	No Collation	None	No Collation	None
Implicit	X	Explicit	Y	Explicit	Y
No Collation	None	Coercible	Default	No Collation	None
No Collation	None	Implicit	X	No Collation	None
No Collation	None	No Collation	None	No Collation	None
No Collation	None	Explicit	X	Explicit	X
Explicit	X	Coercible	Default	Explicit	X
Explicit	X	Implicit	Y	Explicit	X
Explicit	X	No Collation	None	Explicit	X
Explicit	X	Explicit	X	Explicit	X
Explicit	X	Explicit	Y<>X	invalid syntax

Table 7.2 shows how the collating sequence and coercibility attribute are determined for the result of a dyadic operation.

Table 7.3

Collating Sequences used for Comparisons

COMPARAND_1'S COERCIBILITY ATTRIBUTE	COMPARAND_1'S COLLATION	COMPARAND_2'S COERCIBILITY ATTRIBUTE	COMPARAND_2'S COLLATION	COLLATION USED FOR COMPARISON
Coercible	Default	Coercible	Default	Default
Coercible	Default	Implicit	X	X
Coercible	Default	No Collation	None	invalid syntax
Coercible	Default	Explicit	X	X
Implicit	X	Coercible	Default	X
Implicit	X	Implicit	X	X
Implicit	X	Implicit	Y<>X	invalid syntax
Implicit	X	No Collation	None	invalid syntax
Implicit	X	Explicit	Y	Y
No Collation	None	Coercible	Default	invalid syntax
No Collation	None	Implicit	X	invalid syntax
No Collation	None	No Collation	None	invalid syntax
No Collation	None	Explicit	X	X
Explicit	X	Coercible	Default	X
Explicit	X	Implicit	Y	X
Explicit	X	No Collation	None	X
Explicit	X	Explicit	X	X
Explicit	X	Explicit	Y<>X	invalid syntax

(Note: For expressions involving more than two comparands, the collating sequence is effectively determined on a cumulative basis: the result for the first two comparands becomes comparand_1 for the next comparison, the result for this becomes comparand_1 for the comparison after that, and so on.)

Table 7.3 shows how the collating sequence is determined for a particular comparison.

Comments

Comments loading...

Character Strings and Collations

Actions

Links

Images

Table 7.1

Table 7.2

Table 7.3

Comments