Implicit Character Data Type Conversion

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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 Teradata Database 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