Return Types from CONCAT and String Functions

The data type of the return value from a successful call to the CONCAT function (or from the concatenation ( || ) operator, or from a call to other built-in string-manipulation functions that follow the same rules as CONCAT for determining their return type) depends on the data types of the arguments and on the length of the resulting string. The order of the two arguments is not significant in determining the return type.

Informix® applies the following rules for the return type from operations that concatenate values that arguments of more than one data type specify:
  • If one of the types is National Language Support (namely NCHAR and NVARCHAR):
    • the return type is NVARCHAR if the resulting length is less than 255 bytes
    • the return type is NCHAR otherwise.
  • If one of the arguments is VARCHAR or a number type,
    • the return type is VARCHAR if the resulting length is less than 255 bytes
    • the return type is LVARCHAR otherwise.
  • An exception to these rules, however, can occur in certain cross-server operations in which a remote routine is executed locally, and a concatenation expression is evaluated locally before its return value is sent to a remote database server. For remote servers that do not support the LVARCHAR data type in distributed transactions, the concatenated result is sent as a CHAR data type if sending the LVARCHAR type returns an error. Informix® database server instances earlier than Version 11.10 require a CHAR return value in this scenario.

In the following table, the rows list the valid data types of the first argument to the CONCAT function, and the columns list the type of the second argument. The cell at the intersection of each row and column shows the possible returned type or types. The row and the column labelled as Other represent arguments that evaluate to non-character types, such as number or time data types like DECIMAL or DATE.

Table 1. Return Types from Operations on Two Arguments (in Version 11.50.xC2 or Later)
NCHAR NVARCHAR CHAR VARCHAR LVARCHAR Other
NCHAR nchar nvarchar or nchar nchar nvarchar or nchar nvarchar or nchar nvarchar or nchar
NVARCHAR nvarchar or nchar nvarchar or nchar nvarchar or nchar nvarchar or nchar nvarchar or nchar nvarchar or nchar
CHAR nchar nvarchar or nchar char varchar or lvarchar lvarchar varchar or lvarchar
VARCHAR nvarchar or nchar nvarchar or nchar varchar or lvarchar varchar or lvarchar lvarchar varchar or lvarchar
LVARCHAR nvarchar or nchar nvarchar or nchar lvarchar lvarchar lvarchar lvarchar
Other nvarchar or nchar nvarchar or nchar varchar or lvarchar varchar or lvarchar lvarchar varchar or lvarchar

For string manipulation functions other than CONCAT, arguments of DATE, DATETIME, or MONEY data types always return an NVARCHAR or NCHAR value, depending on the length of the resulting string.

This table is symmetrical, because the order of arguments has no affect on the return data type. User-defined data types, large-object types, complex types, and other extended data types are not valid as arguments to the built-in string-manipulation functions or operators.

This table also describes the return data types of expressions that use the concatenation ( || ) operator.

Not shown here is the result of concatenation operations in which the sum of the argument lengths exceeds the approximately 32Kb limit for CHAR, NCHAR, and LVARCHAR data types. This returns error -881, rather than a concatenated data value. Because the maximum LVARCHAR size is 32.739 bytes, and the CHAR and NCHAR limits are both 32,767 bytes, error -881 is usually associated with VARCHAR and NVARCHAR objects, whose limit is 255 bytes, but automatic return type promotion can reduce the incidence of this error.

The following string-manipulation functions support the same rules as CONCAT for return type promotion:
  • LPAD
  • RPAD
  • REPLACE
  • SUBSTR
  • SUBSTRING
  • TRIM
  • LTRIM
  • RTRIM

The following table summarizes how Informix® determines the return type from these string manipulating functions, based on the argument types:

Table 2. String Manipulation Functions that Support Return Type Promotion
Function How the Return Type of the Function is Determined
CONCAT, || Return type is based on both arguments. Refer to Return Types from Operations on Two Arguments (in Version 11.50.xC2 or Later)..
SUBSTR, SUBSTRING Return type is the same as the source string type. If source string is a host variable, the return type is NVARCHAR or NCHAR, depending on the length of the result.
TRIM, LTRIM, RTRIM Return type depends on the source type and the returned length:
  • NVARCHAR returns NVARCHAR
  • VARCHAR returns VARCHAR
  • CHAR returns VARCHAR (if length <= 255 bytes)
  • CHAR returns LVARCHAR (if length > 255 bytes)
  • NCHAR returns NVARCHAR (if length <= 255 bytes)
  • NCHAR returns LVARCHAR (if length > 255 bytes)
  • LVARCHAR returns LVARCHAR
LPAD, RPAD Return type is based on the source_string and pad_string arguments. If pad_string is not specified, the return type is based on the data type of source_string.
REPLACE Return type is based on the source_string and old_string arguments (and on the new_string argument, if that is specified). If any argument is a host variable, the return type is NCHAR.
ENCRYPT_AES, ENCRYPT_TDES, DECRYPT_BINARY, DECRYPT_CHAR, For arguments that are not BLOB or CLOB variables, the return type is based on the data types of the data and encrypted_data arguments. Refer to Return Types from Operations on Two Arguments (in Version 11.50.xC2 or Later).