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 HANA®

SAP HANA provides the following character data types:

  • VARCHAR(N) with N <= 5000 bytes, to store ASCII-7 character strings.
  • NVARCHAR(N) with N <= 5000 characters, to store UNICODE character strings.
  • ALPHANUM(N) with N <= 127 bytes, to store ASCII-7 alpha-numeric character strings.
  • SHORTTEXT(N), supporting text and string search features.
  • CHAR(N)/NCHAR(N): Not officially supported!

The SAP HANA VARCHAR data type must only be used to store ASCII-7 character strings. To store non-ASCII / locale dependent character strings (such as the UTF-8 codeset), you must use the NVARCHAR data type.

Note: The SAP HANA database does not officially support the CHAR and NCHAR datatypes. See SAP HANA documentation for more details.

Solution

Because SAP HANA VARCHAR(N) can only store ASCII-7 characters and does not recommend to use CHAR/NCHAR types, Informix CHAR(N), VARCHAR(N) or LVARCHAR(N) types must be mapped to SAP HANA NVARCHAR(N) when using UTF-8, or a single-byte encoding like ISO-8859-15. In some rare cases where the application character set is ASCII-7 or when only ASCII-7 data is stored in the column, you can use SAP HANA VARCHAR(N) type.

Important: Since SAP HANA does not officially support the CHAR/NCHAR types, all character columns must be SAP HANA VARCHAR (if ASCII-7) or NVARCHAR (if non-ASCII). As the semantics of VARCHAR types are different regarding blank padding, you will get different behavior, when comparing with string values containing trailing blanks.

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

Check that your database schema does not use CHAR or VARCHAR types with a length exceeding the SAP HANA limits especially as the Informix CHAR type has a very long size limit compared to SAP HANA NVARCHAR.

The SAP HANA database interface of Genero automatically converts from/to the application locale to the database locale. Therefore, no database client locale configuration is required with SAP HANA.

See also the section about Localization.