TRIM

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

Read syntax diagramSkip visual syntax diagramTRIM( BOTHBLEADINGLTRAILINGTstrip-characterFROM expression)

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

strip-character
The second argument, if specified, is a single-character constant that indicates the binary, SBCS, or DBCS character that is to be removed. If expression is a binary string, the second argument must be a binary string constant. If expression is a DBCS graphic or DBCS-only string, the second argument must be a graphic constant consisting of a single DBCS character. If the second argument is not specified then:
  • If expression is a binary string, then the default strip character is a hexadecimal zero (X'00').
  • If expression is a DBCS graphic string, then the default strip character is a DBCS blank.
  • If expression is a Unicode graphic string, then the default strip character is a UTF-16 or UCS-2 blank.
  • If expression is a UTF-8 character string, then the default strip character is a UTF-8 blank.
  • Otherwise, the default strip character is an SBCS blank.
expression
An expression that returns a value of any built-in numeric or string data type. A numeric argument is cast to a character string before evaluating the function. For more information about converting numeric to a character string, see VARCHAR.

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

Data type of expression Data type of the Result
CHAR or VARCHAR VARCHAR
CLOB CLOB
GRAPHIC or VARGRAPHIC VARGRAPHIC
DBCLOB DBCLOB
BINARY or VARBINARY VARBINARY
BLOB BLOB

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

If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.

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

The SRTSEQ attribute does not apply to the TRIM function.

Examples

  • 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.
  • 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'
  • Assume the string to be stripped contains mixed data.

    SELECT TRIM( BOTH The argument of the TRIM FROM A string of mixed data )

        FROM SYSIBM.SYSDUMMY1

    Results in: Result of TRIM