17.10 - Implicit Character Data Type Conversion - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

TRANSLATE performs implicit conversion if the string server character set does not match the type implied by source_repertoire_name.

An implicit conversion generates an error if a character from character_string_expression has no corresponding character in the source_repertoire_name type. This holds regardless of whether you specify the WITH ERROR option.

For example, the following function first translates the string from UNICODE to LATIN, because Vantage treats literals as UNICODE, and then translates the string from LATIN to KANJISJIS. However, the translation generates an error because the last character is not in the LATIN repertoire.

   ...
   TRANSLATE('abc ' USING LATIN_TO_KanjiSJIS WITH ERROR)   ...

To circumvent the problem if error character substitution is acceptable, specify two levels of translation, as used in the following example.

   ...
   TRANSLATE((TRANSLATE(_UNICODE 'abc' USING UNICODE_TO_LATIN WITH
ERROR)) USING LATIN_TO_KanjiSJIS WITH ERROR)

Examples

Function Result Type of the Result
TRANSLATE('abc' USING UNICODE_TO_LATIN) 'abc' VARCHAR(3)

CHARACTER SET LATIN

TRANSLATE('abc' USING UNICODE_TO_UNICODE_Fullwidth) 'abc ' VARCHAR(3)

CHARACTER SET UNICODE

TRANSLATE('abc ' USING UNICODE_TO_LATIN WITH ERROR)

where ε represents the designated error character for LATIN (0x1A).

'abcε ' VARCHAR(4)

CHARACTER SET LATIN