The TRIM function

When you dynamically execute a SELECT statement, the DESCRIBE statement can return information about the columns in the select list of the Projection clause at run time. DESCRIBE returns the data type of a select-list column in the appropriate field of the dynamic-management structure that you use.

When you use the DESCRIBE statement on a prepared SELECT statement with the TRIM function in its select list, the data type of the trimmed column that DESCRIBE returns depends on the database server that you use and the data type of the column to be trimmed (the source character-value expression). For more information about the source character-value expression, see the description of the TRIM function in the HCL OneDB™ Guide to SQL: Syntax.

The data type that the DESCRIBE statement returns depends on the data type of the source character-value expression, as identified in the following table:

Table 1. The TRIM function
Sequence number	Operand type	Result type	Result length
1	(N)CHAR(1-255)	(N)VARCHAR	Up to 255
2	(N)CHAR(>255)	LVARCHAR	Up to 32739
3	(N)VARCHAR	(N)VARCHAR	Up to 255
4	LVARCHAR	LVARCHAR	Up to 32739

The following SELECT statement contains the manu_code column, which is defined as a CHAR data type, and the cat_advert column, which is defined as a VARCHAR column. When you describe the following SELECT statement and use the TRIM function, DESCRIBE returns a data type of SQLVCHAR for both trimmed columns:

SELECT TRIM(manu_code), TRIM(cat_advert) FROM catalog;

If the manu_code column is defined as NCHAR instead, DESCRIBE returns a data type of SQLNVCHAR for this trimmed column.

Important: The sqltypes.h header file defines the data type constants SQLCHAR, SQLVCHAR, and SQLNVCHAR. To use these constants, include this header file in your HCL OneDB ESQL/C source file.