DB2 10.5 for Linux, UNIX, and Windows

TRIM scalar function

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--------+  '-strip-character-'                                 
              +-LEADING--+                                                      
              +-L--------+                                                      
              +-TRAILING-+                                                      
              '-T--------'

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')
Otherwise, the default strip-character is an SBCS blank (X'20')

FROM string-expression

The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before evaluating the function.

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'.