SET SESSION COLLATION Syntax Elements - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
collation_sequence
Name of the collation sequence to be established for the session.
For details about collation, see Using Collation with CREATE USER.
ASCII
The collation is ASCII for configurations other than an IBM mainframe client.
ASCII refers to the Teradata extension to the ASCII standard.
CHARSET_COLL
The CHARSET_COLL collation performs binary ordering based on the current client character set. Strings are compared byte-by-byte.
When one comparison string is shorter than the other, it is padded with the pad character defined for the character data type before the comparison is made.
When comparisons are not case-specific, lowercase letters are mapped to their uppercase counterparts before the strings are compared. If the strings are identical, they are equal. Otherwise, the first pair of unequal bytes determines the collating sequence.
When string comparisons involve one or more characters outside the current client character set, the strings are compared and then the characters are checked to determine if all belong to the current client character set, as follows:
Characters in Client Character Set Result
Both Binary ordering of the two characters in the client form-of-use becomes the ordering of the two strings.
One Error character for the character set is used as the collation point for that character.
Neither Binary ordering of the characters (case-specific or not, as appropriate) in the UNICODE form-of-use becomes the ordering of the two strings.
CHARSET_COLL string sorts by character data type behave as follows:
Character Data Type Behavior
KanjiEBCDIC

KanjiShift-JIS

 Ordered as binary sort on client.
KanjiEUC As a Kanji Phase I ASCII collation.

The difference with a binary sort on the client is that JIS X 0208 characters sort before, rather than after, JIS X 0212 characters.
You can specify CHARSET_COLL as the default user collation in CREATE USER (see CREATE USER) or in MODIFY USER (MODIFY USER) if the user definition already exists and you want to change it.
SET SESSION COLLATION CHARSET_COLL overrides user default definitions for the duration of the session in which it is invoked.
EBCDIC
The collation is EBCDIC for an IBM mainframe client.
HOST
The HOST collation orders characters using EBCDIC encoding for IBM mainframe-attached clients and ASCII encoding for all other clients.
JIS_COLL
Japanese Industrial Standards (JIS).
Collation is as follows:
  1. Characters and symbols from the JIS X 0201 standard (in JIS X 0201 order).
  2. Ideographs, characters, and symbols from the JIS X 0208 standard (in JIS X 0208 order).
  3. Ideographs, characters, and symbols from the JIS 0212 standard (in JIS X 0212 order).
  4. User-defined ideographs (in U+ order).
  5. Remaining characters in Unicode (in U+ order).
Treatment of KANJISJIS, GRAPHIC, and LATIN character types is as if the data were first converted to Unicode and then the appropriate JIS_COLL ordering is applied.
MULTINATIONAL
The MULTINATIONAL option is useful only on a European Feature Site or a Japanese language site.
MULTINATIONAL specifies the international sort order to use for the default collation sequence (see International Sort Orders).
MULTINATIONAL returns an error if the hashing algorithm is not set to recognize diacritical characters.
On International/European sites MULTINATIONAL collation is two-level.
On Japanese language sites, collation is single-level and always available.
MULTINATIONAL sets the collation for the session to an international sort sequence that is compatible with either a diacritical character set or a Japanese character set.
Each character in this collation is assigned two integer values. The first indicates the equivalence class of the character and the second indicates the ordering of the character within that class. For example, the letters LATIN CAPITAL LETTER A (Latin A) and LATIN CAPITAL LETTER A WITH GRAVE (Latin À) may belong to the same class, but are ordered differently.
The general ordering follows UNICODE ordering: Latin A precedes Latin B, and so on.
Single-byte MULTINATIONAL collations, such as the Scandinavian languages and other user- installed collations, are extended to include UNICODE characters. The remainder of the UNICODE characters collate the same as the default MULTINATIONAL collation.
When you apply MULTINATIONAL to KANJISJIS, GRAPHIC, or LATIN character data types, the result is as if the data were first converted to UNICODE and the appropriate MULTINATIONAL ordering applied.
Each international sort sequence is defined by the database administrator (and no check is made to make sure that collation is compatible with the character set of the current session) but multibyte character collation sequences cannot be changed.