The LENGTH() function

Informix®

Informix provides the LENGTH() function to count the number of bytes of a character string expression:

SELECT LENGTH("aaa"), LENGTH(col1) FROM table 

Informix LENGTH() does not count the trailing blanks for CHAR or VARCHAR expressions, while Oracle counts the trailing blanks.

Informix LENGTH() returns 0 when the given string is empty. That means, LENGTH('')=0.

SAP HANA®

SAP HANA supports the LENGTH() function, but there are some differences with Informix LENGTH().

The SAP HANA LENGTH() function counts trailing blanks. When using a NCHAR column, values are blank padded, and the function returns the size of the NCHAR column. When using a NVARCHAR column, trailing blanks are significant, and the function returns the number of characters, including trailing blanks.

When passing NULL as parameter, the SAP HANA LENGTH() function returns NULL.

Solution

Check if the trailing blanks are significant when using the LENGTH() SQL function in your application.

To count the number of characters by ignoring the trailing blanks, you must use the RTRIM() function:
SELECT LENGTH(RTRIM(col1)) FROM table
The translation of LENGTH() expressions can be controlled with the following FGLPROFILE entry:
dbi.database.dsname.ifxemul.length = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.