CHAR and VARCHAR data types

Informix®

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)

SAP ASE

SAP® ASE supports following data types to store character data:

Table 2. SAP ASE character data types
SAP ASE data type	Description
`CHAR(n)`	SBCS or MBCS character data using the database character set, where `n` is specified in bytes (max is 16384 bytes)
`VARCHAR(n)`	SBCS or MBCS character data using the database character set, where `n` is specified in bytes (max is 16384 bytes)
`NCHAR(n)`	MBCS character data using the database character set, where `n` is specified in characters (max is 16384 bytes)
`NVARCHAR(n)`	MBCS character data using the database character set, where `n` is specified in characters (max is 16384 bytes)
`UNICHAR(n)`	UNICODE/UCS-2 character data, where `n` is specified in characters (max is 16384 characters)
`UNIVARCHAR(n)`	UNICODE/UCS-2 character data, where `n` is specified in characters (max is 16384 characters)

SAP 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, SAP ASE can store multibyte characters in CHAR/VARCHAR columns, depending on the database character set. For example, SAP can store UTF-8 strings in CHAR/VARCHAR columns. For multibyte character sets, you could also use the NCHAR/NVARCHAR or UNICHAR/UNIVARCHAR SAP ASE types, the only difference with CHAR/VARCHAR is that the length is specified in characters instead of bytes. The UNICHAR/UNIVARCHAR types store characters in 16 bit UCS-2 charset only, but this is transparent to the database client.

SAP ASE supports automatic character set conversion between the client application and the server. By default, the SAP ASE 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 SAP ASE character set.

Unlike most other database engines, SAP 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, it is recommended that you use CHAR/VARCHAR SAP ASE 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 SAP ASE limit.

If your application creates tables with NCHAR/NVARCHAR types, the same type name will be used in SAP ASE. Keep in mind that the size of NCHAR/NVARCHAR in SAP 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 SAP ASE 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 in a SAP ASE 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.

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 VARCHAR columns, make sure that your application does not rely on this non-standard behavior.