TRIM scalar function

The TRIM function removes blanks or another specified character from the end, the beginning, or both ends of a string expression.

The schema is SYSIBM. The function name cannot be specified as a qualified name when keywords are used in the function signature.

BOTH, LEADING, or TRAILING

Specifies whether characters are removed from the beginning, the end, or from both ends of the string expression. If this argument is not specified, the characters are removed from both the end and the beginning of the string.

strip-character

A single-character constant that specifies the character that is to be removed. The strip-character can be any character whose UTF-32 encoding is a single character or a single digit numeric value. The binary representation of the character is matched.

If strip-character is not specified and:

If the string-expression is a DBCS graphic string, the default strip-character is a DBCS blank, whose code point is dependent on the database code page
If the string-expression is a UCS-2 graphic string, the default strip-character is a UCS-2 blank (X'0020')
If the string-expression is a binary string, the default strip-character is a hexadecimal zero (X'00')
Otherwise, the default strip-character is an SBCS blank (X'20')

FROM string-expression

An expression that specifies the string from which the result is derived. The expression must return a value that is a built-in CHAR, VARCHAR, BINARY, VARBINARY, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, BINARY, VARBINARY, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before the function is evaluated.

The value for string-expression and the value for strip-character must have compatible data types.

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

VARCHAR if the data type is VARCHAR or CHAR
VARGRAPHIC if the data type is VARGRAPHIC or GRAPHIC
VARBINARY if the data type is VARBINARY or BINARY

The result is a varying-length string with the same maximum length as the length attribute of the string-expression. The actual length of the result is the length of the string-expression minus the number of string units that are removed. If all of the characters are removed, the result is an empty varying-length string. The code page of the result is the same as the code page of the string-expression.

Examples

Example 1: Assume that the host variable HELLO of type CHAR(9) has a value of ' Hello'.
```
   SELECT TRIM(:HELLO),
     TRIM(TRAILING FROM :HELLO)
   FROM SYSIBM.SYSDUMMY1
```
returns the values 'Hello' and ' Hello', respectively.
Example 2: Assume that the host variable BALANCE of type CHAR(9) has a value of '000345.50'.
```
   SELECT TRIM(L '0' FROM :BALANCE),
   FROM SYSIBM.SYSDUMMY1
```
returns the value '345.50'.