Character Data Types

Character Data Type:
 
                       .-(-- 1--)---.
|--+-+-CHAR---------+--+-(--size--)-+---------------+-----------|
   | +-CHARACTER----+                               |
   | |  (1)         |                               |
   | '--------NCHAR-'                               |
   |    (1)                       .-, 0--------.    |
   +-+--------NVARCHAR---+--(max--+-,--reserve-+--)-+
   | +-VARCHAR-----------+                          |
   | '-CHARACTER VARYING-'                          |
   |  (2)             .-(--2048--)-.                |
   '--------LVARCHAR--+-(--max--)--+----------------'
 

1. Localized Collation
2. Dynamic Server only

The database server issues an error if the data type declaration includes empty parentheses, such as LVARCHAR( ). To declare a CHAR or LVARCHAR data type of the default length, simply omit any (size) or (max) specification. The CREATE TABLE statement of Dynamic Server accepts VARCHAR and NVARCHAR column declarations that have no (max) nor (max, reserve) specifications, using ( 1, 0 ) as the (max, reserve) defaults for the column.

The following table summarizes the built-in character data types.

Single-Byte and Multi-Byte Characters and Locales

All built-in character data types can support single- and multibyte characters in the code set that the DBLOCALE setting specifies. Locales for most European and Middle Eastern languages support only single-byte code sets, but some East Asian locales (and the UTF-8 Unicode locale) support multibyte characters.

The TEXT and CLOB data types also support single-byte or multibyte character data, but most built-in functions for manipulating character strings do not support TEXT nor CLOB data. For more information, see Large-Object Data Types.

Fixed- and Varying-Length Character Data Types

The database server supports storage of fixed-length and varying-length character data. A fixed-length column requires the defined number of bytes regardless of the actual size of the data. The CHAR data type is of fixed-length. For example, a CHAR(25) column requires 25 bytes of storage for all values, so the string "This is a text string" uses 25 bytes of storage.

A varying-length column size can be the number of bytes occupied by its data. NVARCHAR, VARCHAR, and (for Dynamic Server only) the LVARCHAR data types are varying-length character data types. For example, a VARCHAR(25) column reserves up to 25 bytes of storage for the column value, but the character string "This is a text string" uses only 21 bytes of the reserved 25 bytes. The VARCHAR data type can store up to 255 bytes of data.

The LVARCHAR type of Dynamic Server can store up to 32,739 bytes of text. LVARCHAR is a built-in opaque data type. Like other opaque types, it cannot be accessed in a database of a non-local Dynamic Server instance by a distributed query or by other DML operations, nor can LVARCHAR values be returned from a database of another database server by UDRs. For information on accessing LVARCHAR values in other databases of the local server, however, see BOOLEAN and Other Built-In Opaque Data Types (IDS).

A single table cannot be created with more than approximately 195 LVARCHAR columns. (This restriction applies to all varying-length and ROW data types.)

Light scans are not supported on tables that include LVARCHAR columns.

NCHAR and NVARCHAR Data Types

The character data types CHAR, LVARCHAR, and VARCHAR support code-set order collation of data. The database server collates text data in columns of these types by the order that their characters are defined in the code set.

To accommodate locale-specific order of characters, you can use the NCHAR and NVARCHAR data types. The NCHAR data type is the fixed-length character data type that supports localized collation. The NVARCHAR data type is the varying-length character data type that can store up to 255 bytes of text data and supports localized collation. A single table cannot be created with more than approximately 195 NVARCHAR or VARCHAR columns.

Dynamic Server does not support light scans on tables that have NVARCHAR columns.

In Dynamic Server, if you specify no parameters in CREATE TABLE or ALTER TABLE statements that declare VARCHAR or NVARCHAR columns, then the new columns default to a max size of 1 byte and a reserve size of zero.

For more information, see the IBM Informix GLS User's Guide.