CHAR and VARCHAR data types


Informix supports the following character data types:

Table 1. Informix character data types
Informix data type Description
CHAR(n) SBCS and MBCS character data (max is 32767 bytes)
VARCHAR(n[,m]) SBCS and MBCS character data (max is 255 bytes)
NCHAR(n) Same as CHAR, with specific collation order
NVARCHAR(n[,m]) Same as VARCHAR, with specific collation order
LVARCHAR(n) max size varies depending on the IDS version

With Informix, both CHAR/VARCHAR and NCHAR/NVARCHAR data types can be used to store single-byte or multibyte encoded character strings. The only difference between CHAR/VARCHAR and NCHAR/NVARCHAR is in how they use sorting: N[VAR]CHAR types use the collation order, while [VAR]CHAR types use the byte order.

The character set used to store strings in CHAR/VARCHAR/NCHAR/NVARCHAR columns is defined by the DB_LOCALE environment variable.

The character set used by applications is defined by the CLIENT_LOCALE environment variable.

Informix uses Byte Length Semantics (the size N that you specify in [VAR]CHAR(N) is expressed in bytes, not characters as in some other databases)


Netezza supports following data types to store character data:

Table 2. Netezza character data types
Netezza data type Description
CHAR(n) Latin-9 character data, where n is specified in bytes (max is 64000 bytes)
VARCHAR(n) Latin-9 character data, where n is specified in bytes (max is 64000 bytes)
NCHAR(n) Unicode/UTF-8 character data, where n is specified in characters (max is 16000 characters)
NVARCHAR(n) Unicode/UTF-8 character data, where n is specified in characters (max is 16000 characters)

Netezza uses the Latin-9 (ISO-8859-15) code set for CHAR/VARCHAR columns, and UTF-8 for NCHAR/NVARCHAR columns.

No automatic character set conversion is done by the Netezza client software. When using CHAR/VARCHAR columns, the client application character set (LC_ALL, LANG) must match the Latin-9/ISO-8859-15 character set. When using NCHAR/NVARCHAR columns, the client application character set must be UTF-8.


When your application uses a Latin-9 / ISO-8859-15 locale, create tables with the CHAR/VARCHAR SQL types. To store UNICODE (UTF-8) character strings, use the NCHAR/NVARCHAR SQL types instead. In program sources you can use CHAR/VARCHAR; these types can hold single and multibyte character sets, based on the C POSIX locale.

Important: Netezza (V7.2 while writing these lines) supports only the latin9 database character set for CHAR/VARCHAR types. Since character set conversion is not supported, your application can only use Latin-9 or UTF-8.

Table columns using a different character encoding than the database is not supported with Genero: All table columns must use the same character encoding defined at the database level.

Based on the FGLPROFILE entry dbi.database.dbname.ifxemul.nationalchars, the CHAR and VARCHAR type names in SQL statements are converted to CHAR/VARCHAR or to NCHAR/NVARCHAR Netezza types, to store UTF-8 character data.

When using a multibyte character set (such as UTF-8), define database columns as NCHAR and NVARCHAR, with the size in character units, and use character length semantics in BDL programs with FGL_LENGTH_SEMANTICS=CHAR.

When extracting a database schema from a Netezza database, the fgldbsch schema extractor uses the size of the column in characters, not the octet length. If you have created a CHAR(10 (characters) ) column a in Netezza database using the UTF-8 character set, the .sch file will get a size of 10, that will be interpreted by FGL_LENGTH_SEMANTICS as a number of bytes or characters.

With Netezza, it is not possible to defined the database client client character set: The locale used by programs must match the database locale.

See also the section about Localization.

The CHAR/VARCHAR type translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.char = { true | false }
dbi.database.dsname.ifxemul.datatype.varchar = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.