15.10 - FORMAT Phrase and Character Formats - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

The following table shows the default if a CREATE TABLE, ALTER TABLE, or SELECT statement does not include the FORMAT phrase.

 

IF the data type is …

THEN …

CHARACTER

return the full length specified for a fixed‑length CHARACTER column (that is, pad characters are returned unless TRIM is included in the SELECT statement).

VARCHAR

return the actual character string.

CLOB

  • If the server character set is LATIN and the length of the CLOB is < 64000, then return the actual character string.
  • If the server character set is LATIN and the length of the CLOB is 64000, then return the first 64000 characters.
  • If the server character set is UNICODE and the length of the CLOB is < 32000, then return the actual character string.
  • If the server character set is UNICODE and the length of the CLOB is 32000, then return the first 32000 characters.
  • Use the following characters in the FORMAT phrase to control formatting.

     

    Character

    Meaning

    X

    X(n)

    The letter X formats the display of character data. Each X represents one character. The data is returned in left‑to‑right order.

    For example, if a column is defined as FORMAT 'X', only the first (leftmost) character of each field value is displayed.

    Multiple character positions can be defined either by using the (n) notation, where n defines the length of the string, or by entering the equivalent number of X characters.

    Character strings are padded or truncated on the right when they are shorter or longer than the positions defined in the FORMAT phrase.

    The following table shows how FORMAT 'X(n)' applies to server character sets.

     

    IF a string expression has this character set …

    THEN FORMAT 'X(n)' returns …

    LATIN

    GRAPHIC

    UNICODE

    n logical characters.

    KANJI1

    KANJISJIS

    n bytes.

    The character string in a FORMAT clause cannot be of type KANJI1. The form FORMAT
    _Kanji1 ‘
    hex_digits’XC in particular always generates an error.

    It is possible to truncate the output of multibyte characters in an expression with KANJI1 server character set with any of the following client character sets:

  • KanjiEBCDIC
  • KanjiEUC
  • KanjiShift-JIS
  • In ANSI mode, an error occurs if a non-blank character is truncated.

    In Teradata mode, the system returns the number of bytes specified and does not tell you when truncation occurs. The table below shows the hexadecimal returns. The actual return display depends on which system is used.

     

    This character …

    With this format …

    Returns this value in hexadecimal …

    With this character set …

    B<AA>

    'xx'

    C20E

    KanjiEBCDIC

    BAA

    'xx'

    42A3

    KanjiEUC

    Notice:

    KANJI1 support is deprecated. KANJI1 is not allowed as a default character set. The system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible.

    Both of the following FORMAT phrases specify a five‑position character string:

       FORMAT 'X(5)'
       FORMAT 'XXXXX'

    Assume your data is the character string ‘HELLO’.

     

    IF the format is …

    THEN this is displayed …

    FORMAT 'X(6)'

    HELLO with trailing blank

    FORMAT 'X'

    H