CHARACTER data types

Informix^® supports the following character data types:

• CHAR(N) with N<= 32767 bytes
• VARCHAR(N[,M]) with N<=255 bytes
• NCHAR(N) with N<= 32767 bytes
• NVARCHAR(N[,M]) with N<=255 bytes
• LVARCHAR(N), without the 255 bytes limit (max size varies according to IDS version)

In 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 for 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.

ORACLE provides the following types to store character strings:

• CHAR(N) with N specified in bytes or characters according to the length semantics (max size is 2000 bytes)
• VARCHAR2(N) with N specified in bytes or characters according to the length semantics (max size is 4000 bytes - standard type)
• NCHAR(N) with N specified in characters (max size is 2000 bytes)
• NVARCHAR2(N) with N specified characters (max size is 4000 bytes - standard type)

In ORACLE CHAR(N)/VARCHAR2(N) types, the size N can be specified in character or byte units, according to length semantics settings.

When comparing CHAR and VARCHAR2 values in ORACLE, the trailing blanks are significant; this is not the case when using Informix VARCHARs. However, before comparing string values, ORACLE blank-pads CHAR(N) data to the maximum length of both operands. As result, it looks like trailing blanks are no significant in CHAR(N) comparison. For example, a column defined as CHAR(5) with the value 'abc ' (with 2 trailing blanks) will not be equal to 'abc', but when comparing (col = 'abc'), ORACLE will add 2 blanks to the right operand and values will match. Blank padding does not occur for VARCHAR2() data, as result, the expression (col = 'abc') will be false, if col VARCHAR2 does not exactly contain the value 'abc'. For more details, see blank-padded and non-padded comparison semantics in ORACLE documentation.

ORACLE treats empty strings like NULL values; Informix doesn't. See issue Empty Character Strings for more details.

With ORACLE, you can define a Database Character Set and a National Character Set: ORACLE uses the Database Character Set to store string data in the CHAR/VARCHAR2 columns, and uses the National Character Set for NCHAR/NVARCHAR2 columns.