TRIM Function

The TRIM function removes specified leading or trailing pad characters from a string. (See also the descriptions of the LTRIM and RTRIM functions, which provide similar functionality, but support a different syntax.)
TRIM Function

1  TRIM  (? 
2.1!  BOTH
2.1 TRAILING
2.1 LEADING
1?  pad_char
1 FROM source_expression)
Element Description Restrictions Syntax
pad_char Expression that evaluates to a single character or NULL. The default is a blank space ( = ASCII 32). Must be a character expression Expression
source _expression Character expression, including a character column name, or a call to another TRIM function Cannot be a DISTINCT data type Expression

The TRIM function returns a character string identical to its source_expression argument, except that any leading or trailing pad characters, as specified by the LEADING, TRAILING, or BOTH keywords, are deleted. If no trim qualifier (LEADING, TRAILING, or BOTH) is specified, BOTH is the default. If no pad_char is specified, a single blank space (the ASCII 32 character) is the default, and leading or trailing blank spaces, as specified by the qualifying keyword, are deleted from the returned value.

If either the pad_char or the source_expression evaluates to NULL, the result of the TRIM function is NULL.

The data type of the returned value depends on the source_expression argument:

  • If the argument is longer than 255 bytes, the returned value is of type LVARCHAR.

  • If the argument has 255 bytes or fewer, the data type of the returned value depends on the data type of the argument:
    • If the argument is of type CHAR or VARCHAR, a VARCHAR value is returned.
    • If the argument is of type NCHAR or NVARCHAR, an NVARCHAR value is returned.
    • If the argument is of type LVARCHAR, an LVARCHAR value is returned.
The length of the returned value is 255 bytes or fewer for VARCHAR or NVARCHAR source_expression arguments, and no more than 32,739 bytes for CHAR, NCHAR, or LVARCHAR arguments.
The following example shows some generic uses for the TRIM function: 
SELECT TRIM (c1) FROM tab;
SELECT TRIM (TRAILING '#' FROM c1) FROM tab;
SELECT TRIM (LEADING FROM c1) FROM tab;
UPDATE c1='xyz' FROM tab WHERE LENGTH(TRIM(c1))=5;
SELECT c1, TRIM(LEADING '#' FROM TRIM(TRAILING '%' FROM
   '###abc%%%')) FROM tab;

In Dynamic SQL, when you use the DESCRIBE statement with a SELECT statement that calls the TRIM function in the Projection list, the data type of the trimmed column that DESCRIBE returns depends on the data type of the source_expression, for SQL data type constants defined in the sqltypes.h header file of the source file. For further information on the GLS aspects of the TRIM function in , see the HCL OneDB™ GLS User's Guide.