15.00 - TRANSLATE_CHK - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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