CHARACTER data types

Informix^® supports following character data types:

• CHAR(N) with N<= 32767 bytes
• VARCHAR(N[,M]) with N<=255 bytes
• NCHAR(N) with N<= 32767 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)

Sybase ASE implements the following character data types:

• CHAR(N) with N <= 16384 bytes
• VARCHAR(N) with N <= 16384 bytes
• NCHAR(N) with N <= 16384 characters
• NVARCHAR(N) with N <= 16384 characters
• UNICHAR(N) with N <= 16384 characters
• UNIVARCHAR(N) with N <= 16384 characters

Like Informix, Sybase ASE can store multibyte characters in CHAR / VARCHAR columns, according to the database character set. For example, Sybase can store UTF-8 strings in CHAR/VARCHAR columns. For multibyte character sets, you could also use the NCHAR / NVARCHAR or UNICHAR / UNIVARCHAR Sybase ASE types, the only difference with CHAR / VARCHAR is that the length is specified in characters instead of bytes. The UNICHAR / UNIVARCHAR store characters in 16bit UCS-2 charset only, but this is transparent to the database client.

Sybase supports automatic character set conversion between the client application and the server. By default, the Sybase database client character set is defined by the operating system locale where the database client runs. On Windows™, it is the ANSI code page of the login session (can be overwritten by setting the LANG environment variable), on UNIX™ it is defined by the LC_CTYPE, LC_ALL or LANG environment variable. You may need to edit the $SYBASE/locales/locales.dat file to map the OS locale name to a known Sybase character set.

Unlike most other database engines, Sybase ASE trims trailing blanks when inserting character strings in a VARCHAR column.

For example:

CREATE TABLE t1 ( k INT, vc VARCHAR(5))
INSERT INTO t1 VALUES ( 1, 'abc ' )
SELECT '['||vc||']' FROM t1 WHERE k = 1
------------------------------------------------
[abc]

With other database servers you would get 1 blank after abc:

[abc ]