MULTINATIONAL Collation - Teradata Database

International Character Set Support

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-09-25
dita:id
B035-1125
lifecycle
previous
Product Category
Teradata® Database

The collation sequence for MULTINATIONAL collation can be any one of the following:

  • Teradata Standard Multinational (a two-level comparison based on the Unicode collation standard).
  • 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.

  • Norwegian Standard (based on the Norwegian collation sequence).
  • Swedish Standard (based on the Swedish collation sequence).
  • Any one of three predefined Japanese collations:
  • Katakana_Standard
  • Kanji5026_Standard
  • Kanji5035_Standard
  • A site-defined collation sequence.
  • 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:

  • All characters in an equivalent class have the same collation value. A character from class i is less than any character from class i+1.
  • If two strings are equal, based upon the class, additional processing takes place to order the characters within a class.
  • 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