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: 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: |
|||
|
|
|||
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 |
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 |