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 |