15.00 - Implicit Character-to-Character Translation - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Implicit Character-to-Character Translation

Implicit string translation occurs when two character strings are incompatible within a given operation. For example,

   SELECT *
   FROM string_table
   WHERE clatin < csjis;

where clatin represents a character column defined as CHARACTER SET LATIN and csjis represents a character column defined as CHARACTER SET KANJISJIS.

If an implicit translation of character string ‘string’ to a UNICODE character string is required, it is equivalent to executing the TRANSLATE(string USING source_repertoire_name_TO_Unicode) function, where source-repertoire-name is the server character set of string.

More specifically, if as in the above example, string is of KANJISJIS type, then the translation is equivalent to executing the TRANSLATE(string USING KanjiSJIS_TO_Unicode) function.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Character Literals

The following rules apply to implicit character-to-character translation involving character literals.

 

IF one operand is a …

AND the other operand is a …

THEN …

literal

literal

both operands are translated to UNICODE.

non-literal

the literal is translated to the type of the non-literal. If that fails, both are translated to UNICODE.

literal expression

the literal is translated to the type of the literal expression. If that fails, both are translated to UNICODE.

literal expression

literal expression

both operands are translated to UNICODE.

non-literal

the literal expression is translated to the type of the non-literal. If that fails, both are translated to UNICODE.

non-literal

non-literal

both operands are translated to UNICODE.

KANJISJIS Server Character Set

Implicit character-to-character translation always converts a character string argument that has the KANJISJIS server character set to UNICODE.

SQL Rules for Implicit Translation for Expression and Function Arguments

The following are the rules for implicit translation between types of expressions and function arguments.

For string functions that produce a character result, the results are summarized by this table.

 

FOR this function …

The result is …

TRIM

converted back to the type of the main string argument (last argument).

|| (concatenation)

not translated and remains with the character data type of the arguments after any implicit translation.

Note that the other string functions either do not involve conversion or the type of the result is based on the function and not the server character set of the argument.

For example, in the following TRIM function, <unicode-literal> is first translated to Latin, and then the trim operation is performed.

   ...
   TRIM(<unicode-literal> FROM <latin-value>)

The result is Latin.