15.00 - TRANSLATE_CHK - Teradata Database

TRANSLATE_CHK

Purpose  

Determines if a TRANSLATE conversion can be performed without producing errors; returns an integer test result. Use TRANSLATE_CHK to filter untranslatable strings. You can choose to select translatable strings only, or untranslatable strings only, depending on how you form your SELECT statement.

Syntax  

where:

Syntax element …	Specifies …
character_string_expression	a character string to be translated to another server character set. If character_string_expression is not a character type, an error is returned.
source_repertoire_name	the source character set of the string to be translated. For supported values, see “Supported Translations Between Character Sets” on page 1259. A value of LOCALE can be specified for source_repertoire_name to translate a character string from LATIN or KANJI1 to UNICODE using a source repertoire determined by the language support mode of the system and the client character set of the session. For details, see “Supported Translations Between Character Sets” on page 1259.
_encoding	an optional literal for translating from KANJI1 to UNICODE that indicates a specific encoding of KANJI1. The _encoding option is not allowed if LOCALE is specified for source_repertoire_name or target_repertoire_name.
_encoding (continued)	If the translation is from the following character sets: KatakanaEBCDIC KanjiEBCDIC5026_0I KanjiEBCDIC5038_0I use the following value for _encoding: _KanjiEBCDIC KanjiEUC_0U, use the following value for _encoding: _KanjiEUC KanjiShiftJIS_0S, us the following value for _encoding: _KANJISJIS ASCII or EBCDIC, use the following value for _encoding: _SBC
target_repertoire_name	the target character set of the string to translate. For supported values, see “Supported Translations Between Character Sets” on page 1259. A value of LOCALE can be specified for target_repertoire_name to translate a character string from UNICODE to LATIN or KANJI1 using a target repertoire determined by the language support mode of the system and the client character set of the session. For details, see “Supported Translations Between Character Sets” on page 1259.
_suffix	that the translation maps some source characters to semantically different characters. For example, a translation that specifies the _Halfwidth suffix maps any character with a halfwidth variant to that variant, and all fullwidth variants to their non-fullwidth counterparts. The _suffix option also indicates the form of character data translated from UNICODE to the KANJI1 server character set, for example, _KanjiEUC. Valid values are:
	_KanjiEBCDIC _KanjiEUC _KANJISJIS _SBC _PadSpace		_PadGraphic _Fullwidth _Halfwidth _FoldSpace _VarGraphic
	The _suffix option is not allowed if LOCALE is specified for source_repertoire_name or target_repertoire_name.

ANSI Compliance

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

Argument Types

Use TRANSLATE_CHK on character strings and character string expressions.

By default, Teradata Database performs implicit type conversion on UDT arguments that have implicit casts to predefined character types.

To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

Implicit type conversion of UDTs for system operators and functions, including TRANSLATE_CHK, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”

Result Type and Attributes

Default attributes for TRANSLATE_CHK (string USING source_TO_target) are:

Data Type	Heading
INTEGER	Translate_Chk(string using source_to_target)

Result Values

Value	Meaning
0	The string can be translated without error.
NULL	The string result is null.
anything else	The position of the first character in the string causing a translation error. The value is a logical position for arguments of type LATIN, UNICODE, KANJISJIS, and GRAPHIC. The value is a physical position for arguments of type KANJI1.

Example  

Function	Result
TRANSLATE_CHK(‘abc’ USING UNICODE_TO_LATIN)	0
TRANSLATE_CHK(‘abc’ USING UNICODE_TO_LATIN)	4

Example  

Consider the following table definition:

   CREATE TABLE table_1

     (cunicode CHARACTER(64) CHARACTER SET UNICODE);

To find all values in cunicode that can be translated to LATIN, use the following statement:

   SELECT cunicode

   FROM table_1

   WHERE TRANSLATE_CHK(cunicode USING Unicode_TO_Latin) = 0;

Example  

Consider the following table definitions:

   CREATE TABLE table_1

     (ckanji1 VARCHAR(20) CHARACTER SET KANJI1);

   CREATE TABLE table_2

    (cunicode CHARACTER(20) CHARACTER SET UNICODE);

Assume table_1 is populated from the KanjiEUC client character set.

To translate the data in ckanji1 in table_1 to UNICODE, and populate table_2 with translations that have no errors, use the following statement:

   INSERT INTO table_2

   SELECT TRANSLATE(ckanji1 USING Kanji1_KanjiEUC_TO_Unicode)

   FROM table_1

   WHERE TRANSLATE_CHK(ckanji1 USING Kanji_KanjiEUC_TO_Unicode) = 0;

Example  

After converting column ckanji1 in table_1 to column cunicode in table_2, you want to find all the fields in table_1 that could not be translated.

   SELECT ckanji1

   FROM table_1

   WHERE TRANSLATE_CHK(ckanji1 USING Kanji1_KanjiEUC_TO_Unicode) <> 0;

Checking UNICODE Normalization Form Translations

When using TRANSLATE_CHK to verify UNICODE normalization form translations, any valid Unicode string can be translated to any of the normalization forms. A successful result (0) is expected unless the compatibility ideographs U+FA6C, U+FACF, U+FAD0, U+FAD1, U+FAD5, U+FAD6, or U+FAD7 are present, because these characters normalize outside the BMP, that is, outside the range U+10000 to U+10FFF.

For all normalization forms, these characters normalize as follows:

Ideograph	Normalized Form
U+FA6C	U+242EE
U+FACF,	U+2284A
U+FAD0	U+22844
U+FAD1	U+233D5
U+FAD5	U+25249
U+FAD6	U+25CD0
U+FAD7	U+27ED3