TRIM

The TRIM function removes bytes from the beginning, from the end, or from both the beginning and end of a string expression.

>>-TRIM--(--+---------------------------------------+--string-expression--)-><
            | .-BOTH-----.                          |                         
            '-+----------+--+---------------+--FROM-'                         
              +-B--------+  '-trim-constant-'                                 
              +-LEADING--+                                                    
              +-L--------+                                                    
              +-TRAILING-+                                                    
              '-T--------'

The schema is SYSIBM.

The first argument, if specified, indicates whether characters are removed from the end or the beginning of the string. If the first argument is not specified, the characters are removed from both the end and the beginning of the string.

trim-constant

Specifies a constant that indicates the binary, SBCS, or DBCS character that is to be removed. If string-expression is a character string, trim-constant must be an SBCS or DBCS single-character (2 bytes) constant. If string-expression is a binary string, trim-constant must be a single-byte binary string constant. If string-expression is a DBCS graphic or DBCS-only string, trim-constant must be a graphic constant that consists of a single DBCS character.

The default for trim-constant depends on the data type of string-expression:

A DBCS blank if string-expression is a DBCS graphic string. For ASCII, the CCSID determines the hex value that represents a DBCS blank. For example, for Japanese (CCSID 301), X'8140' represents a DBCS blank, while for Simplified Chinese, X'A1A1' represents a DBCS blank. For EBCDIC, X'4040' represents a DBCS blank.
A UTF-16 or UCS-2 blank (X'0020') if string-expression is a Unicode graphic string.
A value of X'00' if string-expression is a binary string.
Otherwise, a single byte blank. For EBCDIC, X'40' represents a blank. When not EBCDIC, X'20' represents a blank.

string-expression

An expression that returns a value that is a built-in character string data type, graphic data type, binary string data type, or numeric data type. string-expression must not be a LOB. If string-expression is numeric, it is cast to a character string before the function is evaluated. For more information about converting numeric to a character string, see VARCHAR.

string-expression and trim-expression must have compatible data types.

The data type of the result depends on the data type of string-expression:

If string-expression is a character string data type, the result is VARCHAR. If string-expression is defined as FOR BIT DATA the result is FOR BIT DATA.
If string-expression is a graphic string data type, the result is VARGRAPHIC.
If string-expression is a binary string data type, the result is VARBINARY.

The length attribute of the result is the same as the length attribute of string-expression. The actual length of the result is the length of string-expression minus the number of characters removed. If all of the characters are removed, the result is an empty string.

If string-expression can be null, the result can be null; if string-expression is null, the result is the null value.

The CCSID of the result is the same as that of string-expression.

Example: Assume the host variable HELLO of type CHAR(9) has a value of ' Hello '.

SELECT TRIM(:HELLO), TRIM(TRAILING FROM :HELLO)
		FROM SYSIBM.SYSDUMMY1

Results in 'Hello' and ' Hello' respectively.

Example: Assume the host variable BALANCE of type CHAR(9) has a value of '000345.50'.

SELECT TRIM(L '0' FROM :BALANCE)
		FROM SYSIBM.SYSDUMMY1

Results in '345.50'