17.00 - CHARSET_COLL - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

HOST.

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, the following process occurs.

  1. Lowercase letters are mapped to their uppercase counterparts.
  2. The strings are compared.
IF the strings are … THEN the …
identical equality relation holds.
not identical first pair of bytes that is not equal determines the collating sequence.

When string comparisons involve one or more characters outside the current client character set, then the following behavior occurs.

  1. The strings are compared.
  2. Characters are checked to determine if they are all within the repertoire of the current client character set.
IF the characters compared have this relationship to the client character set … THEN …
both are in the binary ordering of the two characters in the client form-of-use becomes the ordering of the two strings.
one is not in the error character for the character set is used as the collation point for that character.
neither is in the binary ordering of the characters, either case blind or case specific, 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.

FOR this character data type … CHARSET_COLL collation orders characters as follows …
Kanji1 Single-byte characters based on the current character set.

Multibyte characters based on their internal value.

KANJI1 character data types can contain mixed single-byte/multibyte character sets.

Single-byte characters in KANJI1 are translated into the form-of-use.

Multibyte characters in KANJI1 are not translated.

KanjiEBCDIC
KanjiShift-JIS As a binary sort on the client would be ordered.
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 wish to change it.

SET SESSION COLLATION CHARSET_COLL overrides any such user default definitions for the duration of the session in which it is invoked.