15.10 - VARCHAR Data Type - Teradata Database

Teradata Database SQL Data Types and Literals

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

Represents a variable length character string of length 0 to n for Teradata Database internal character storage. LONG VARCHAR specifies the longest permissible variable length character string for Teradata Database internal character storage.

where:

 

Syntax element …

Specifies …

n

the maximum number of characters or bytes allotted to the column defined with this server character set:

  • For the LATIN server character set, the maximum value for n is 64000 characters.
  • For the UNICODE and GRAPHIC server character sets, the maximum value for n is 32000 characters.
  • For the KANJISJIS server character set, the maximum value for n is 32000 bytes.
  • server_character_set

    the server character set for the character column being defined.

    If the CHARACTER SET server_character_set clause is omitted, the default server character set depends on how the user is defined in the DEFAULT CHARACTER SET clause of the CREATE USER statement. See “CREATE USER” in SQL Data Definition Language.

    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.

    Supported values for server_character_set are as follows:

  • LATIN represents fixed 8-bit characters from the ASCII ISO 8859 Latin1 or ISO 8859 Latin9 repertoires.
  • See “LATIN Server Character Set” on page 228.

  • UNICODE represents fixed 16-bit characters from the UNICODE 6.0 standard.
  • See “UNICODE Server Character Set” on page 229.

  • GRAPHIC represents fixed 16-bit UNICODE characters defined by IBM Corporation for DB2.
  • See “GRAPHIC Server Character Set” on page 230.

  • KANJISJIS represents mixed single byte/multibyte characters intended for Japanese applications that rely on KanjiShiftJIS characteristics.
  • See “KANJISJIS Server Character Set” on page 231.

    attributes

    appropriate data type, column storage, or column constraint attributes.

    See “Core Data Type Attributes” on page 17 and “Storage and Constraint Attributes” on page 18 for specific information.

    VARCHAR is ANSI SQL:2011 compliant.

    LONG VARCHAR, VARGRAPHIC, and LONG VARGRAPHIC are Teradata extensions to the ANSI SQL:2011 standard.

    Character data is allocated either in terms of characters or in terms of bytes, depending on the server character set used. The number of bytes of storage per character also varies depending on the server character set, as illustrated by the following table.

     

    Server Character Set

    Server Form-of-Use

    Server Space Allocation

    Sharable Among Heterogeneous Clients?

    LATIN

    Fixed 8-bit LATIN

    Character-based

     

     

    Yes

     

     

    UNICODE

    Fixed16-bit UNICODE

    GRAPHIC

    Fixed 16-bit UNICODE

    KANJISJIS

    Mixed single and multibyte KANJISJIS

    Byte-based

    Yes

    Any conversion to or from client system data types is done by Teradata Database. This data type supports international character sets.

    Whenever a client application talks to Teradata Database, it indicates its character set (form-of-use for character data). The server returns all character data to the application in that form.

    For information on the number of bytes exported for the VARCHAR type, see “Exported Character Data” on page 225.

    For information on the number of bytes exported for the LONG VARCHAR type, see “Exported Character Data” on page 225.

    The following table shows how LONG VARCHAR data is represented for the various server character sets. Apart from these definitions, LONG VARCHAR strings behave identically to VARCHAR strings.

     

    FOR this server character set …

    The external representation for LONG VARCHAR is equivalent to …

  • LATIN
  • KANJI1
  • VARCHAR(64000)

  • UNICODE
  • GRAPHIC
  • KANJISJIS
  • VARCHAR(32000)

    The following table lists the client representation for the IBM DB2 VARGRAPHIC type.

    Determining the application definitions and client data types is the responsibility of the application programmer.

    Define the length of the VARGRAPHIC(n) string as k, where 0 <= k <= n.

     

    Client CPU Architecture

    Client Internal Data Format

    IBM mainframe

    Two byte SMALLINT length count k followed by k DB2 GRAPHIC characters for a total of 2+2k EBCDIC bytes.

    The default display format for VARCHAR(n) is X(n).

    The default display format for LONG VARCHAR is either X(32000) or X(64000) depending on the server character set in effect.

    For details, see “Data Type Default Formats” on page 281.

    You can use the VARGRAPHIC or LONG VARGRAPHIC to represent multibyte character data.

    VARGRAPHIC(n) is equivalent to VARCHAR(n) CHARACTER SET GRAPHIC. For best practice, define all VARGRAPHIC(n) data as VARCHAR(n) CHARACTER SET GRAPHIC.

    The maximum value for n in a VARCHAR(n) CHARACTER SET GRAPHIC definition is 32000. There is no default length; therefore, omitting the length specification results in an error.

    LONG VARGRAPHIC is equivalent to LONG VARCHAR CHARACTER SET GRAPHIC. For best practice, define all LONG VARGRAPHIC data as LONG VARCHAR CHARACTER SET GRAPHIC.

     

    FOR information on …

    SEE …

    character literals

    “Character String Literals” on page 88.

    conversion of external-to-internal and internal-to-external character data, including truncation and error handling

    International Character Set Support.

    The following statement creates a table that defines two VARCHAR columns: InfoKey and InfoData.

       CREATE TABLE InfoTable
         (InfoKey VARCHAR(10) NOT NULL
         ,InfoData VARCHAR(16384) )
       UNIQUE PRIMARY INDEX ( InfoKey );

    The following statements insert character data of varying lengths into the InfoKey and InfoData columns:

       INSERT INTO InfoTable ('001_5_799', 'Data for key 001_5_799');
       INSERT INTO InfoTable ('2', 'Data for key 2');

    The following statement creates a table that defines a LONG VARCHAR column called InfoData.

       CREATE TABLE InfoTable (InfoData LONG VARCHAR);