The collation sequence for MULTINATIONAL collation can be any one of the following:
This is the initial default sequence for any user or session with [SESSION] COLLATION set to MULTINATIONAL.
It can be altered using the macro CollAddStandard.
MULTINATIONAL collation is two level except when used with KANJI1 data, where the collation is single level. Teradata, Norwegian, and Swedish standard collations are designed as two level, while all Japanese collations are single level.
Use collations designed as single level on KANJI1 columns only. Do not use collations designed as two level on KANJI1 columns.
The comparison of two character strings under MULTINATIONAL collation obeys the following rules:
As an example using Teradata Standard Multinational collation, consider the following characters: b, B, c, C, ç, Ç, d and D.
Teradata Standard Multinational collation considers these characters … |
To be in the following equivalence class … |
b B |
B |
c C ç Ç |
C |
d D |
D |
Within the equivalence class of … |
The case-specific ordering is … |
B |
1 b 2 B |
C |
1 c 2 C 3 ç 4 Ç |
D |
1 d 2 D |
The following table shows the internal values of the characters b, B, c, C, ç, Ç, d and D, and their corresponding values in CollEqvClass, CollOrderCS, and CollOrderUC.
In this table, the values assigned to the equivalence class of b, c, and d are 0x62, 0x63, and 0x64, respectively. This assures that b sorts before c, and c before d. The case-specific (CollOrderCS) value for c is 0x00, and the case-specific value for C is 0x01. This assures that, using the second-level comparison, c sorts before C.
For details on ordering characters within classes as performed by the Teradata Standard Multinational collation, see “ORDER BY Clause” in “The SELECT Statement” in SQL Data Manipulation Language.
Character |
Hexadecimal Value |
|||
Teradata Internal Value |
CollEqv Class |
CollOrder Casespecific |
CollOrder Uppercase |
|
b |
0x62 |
0x62 |
0x00 |
0x01 |
B |
0x42 |
0x62 |
0x01 |
0x01 |
c |
0x63 |
0x63 |
0x00 |
0x01 |
C |
0x43 |
0x63 |
0x01 |
0x01 |
ç |
0xE7 |
0x63 |
0x02 |
0x01 |
Ç |
0xC7 |
0x63 |
0x03 |
0x01 |
d |
0x64 |
0x64 |
0x00 |
0x01 |
D |
0x44 |
0x64 |
0x01 |
0x01 |
For Japanese language support, MULTINATIONAL collation is single level.
You can specify MULTINATIONAL as the default user collation with the CREATE and MODIFY USER statements.
You can also use the SQL SET SESSION COLLATION MULTINATIONAL statement to override any user defaults.
For information on... |
See... |
Teradata Database collating conventions |
“Comparison Operators” and “Comparisons for KANJI1 Characters” in SQL Functions, Operators, Expressions, and Predicates |
“ORDER BY Clause” in SQL Data Manipulation Language |