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 ]

Solution

If your application must support multibyte character sets like BIG5 or UTF-8, you should use CHAR / VARCHAR Sybase data types, where the length is specified in bytes like with Informix.

Check that your database schema does not use CHAR, VARCHAR or LVARCHAR types with a length exceeding the Sybase ASE limit.

If your application creates tables with NCHAR/NVARCHAR types, the same type name will be used in Sybase. Keep in mind that the size of NCHAR/NVARCHAR in Sybase is specified in characters, while Informix uses a number of bytes.

When using a multibyte character set (such as UTF-8), define database columns 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 Sybase database, the 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 Sybase database using the UTF-8 character set, the .sch file will get a size of 10, that will be interpreted according to FGL_LENGTH_SEMANTICS as a number of bytes or characters.

Do not forget to properly define the database client character set, which must correspond to the runtime system character set.

Since trailing blanks are trimmed for VARCHARs, make sure that your application does not rely on this non-standard behavior.