TRIM function The TRIM function takes a character expression and returns that expression with leading and/or trailing pad characters removed. functionsTRIM TRIM function

Optional parameters indicate whether leading, or trailing, or both leading and trailing pad characters should be removed, and specify the pad character that is to be removed.

Syntax TRIM( [ trimOperands ] trimSource )

The trimSource is a characterExpression.
trimOperands { trimType [ trimCharacter ] FROM | trimCharacter FROM }

The trimCharacter is a characterExpression.

trimType { LEADING | TRAILING | BOTH }

If trimType is not specified, it defaults to BOTH. If trimCharacter is not specified, it will default to the space character (' '). Otherwise the trimCharacter expression must evaulate to one of the following:

  • A character string whose length is exactly one
  • NULL

If either trimCharacter or trimSource evaluates to NULL, the result of the TRIM function is NULL. Otherwise, the result of the TRIM function is defined as follows:

  • If trimType is LEADING, the result will be the trimSource value with all leading occurrences of trimCharacter removed.
  • If trimType is TRAILING, the result will be the trimSource value with all trailing occurrences of trimCharacter removed.
  • If trimType is BOTH, the result will be the trimSource value with all leading and trailing occurrences of trimCharacter removed.

If trimSource's data type is CHAR or VARCHAR, the return type of the TRIM function will be VARCHAR. Otherwise the return type of the TRIM function will be CLOB.

Examples

-- returns 'derby' (no spaces) VALUES TRIM(' derby ')

-- returns 'derby' (no spaces) VALUES TRIM(BOTH ' ' FROM ' derby ')

-- returns 'derby ' (with a space at the end) VALUES TRIM(LEADING ' ' FROM ' derby ')

-- returns ' derby' (with two spaces at the beginning) VALUES TRIM(TRAILING ' ' FROM ' derby ')

-- returns NULL VALUES TRIM(cast (null as char(1)) FROM ' derby ')

-- returns NULL VALUES TRIM(' ' FROM cast(null as varchar(30)))

-- returns ' derb' (with a space at the beginning) VALUES TRIM('y' FROM ' derby')

-- results in an error because trimCharacter can only be 1 character VALUES TRIM('by' FROM ' derby')