Character Strings
Open SQL supports three different types to store varying length character strings: VARCHAR, LONGVARCHAR and CLOB. Only columns of the VARCHAR type, whose maximum size is limited to 1000 characters, are allowed to occur in comparison expressions, ORDER BY clauses, and so on.
From a functional point of view, LONGVARCHAR and CLOB columns behave the same, but the size restriction imposed on the LONGVARCHAR type allows this type to be mapped to an appropriate VARCHAR data type on each supported database platform. On most database platforms, this type is more efficient than the respective CLOB type.
To store character strings with more than 1,333 characters, the CLOB type must be used.
For portability reasons, Open SQL does not permit the storage of empty (zero-length) strings in any of these column types, because on some database platforms the empty string is always treated as a NULL value, which would lead to different query behavior on different database platforms. Furthermore, Open SQL forbids the storage of string values with trailing blanks in VARCHAR and LONGVARCHAR columns. This is because comparison semantics differs between blank-padded and non-padded semantics on the various database platforms. Blank-padded semantics compares two strings as equal if they differ in the number of trailing blanks only – that is, "ABC” equals “ABC “, whereas non-padded semantics compares two strings as equal only if they have equal length and no differing characters – that is, “ABC” is not equal to “ABC “.
If an application needs to represent something like an "initial" value in a VARCHAR or LONGVARCHAR column, it is recommended that you use the string " " (a String object consisting of one single space). Although this is, strictly speaking, also a blank-padded string, it is accepted by Open SQL and can be treated in a portable way across all supported database platforms. In the case of CLOB columns, an initial value can and should be represented as a NULL value explicitly.
More Information