TRIM

The TRIM function that takes a character expression and returns that expression with leading and/or trailing pad characters removed. 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)

trimOperands

{  { trimType [trimCharacter]  FROM
  |  trimCharacter FROM
}

trimCharacter

A character expression that specifies which character to trim from the source. If this is specified, it must evaluate to either NULL or to a character string whose length is exactly one. If left unspecified, it defaults to the space character (' ').

trimType

{LEADING | TRAILING | BOTH}

If this value is not specified, the default value of BOTH is used.

trimSource

The character expression to be trimmed

Results

If either trimCharacter or trimSource evaluates to NULL, the result of the TRIM function is NULL. Otherwise, the result 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

splice> VALUES TRIM('      Space Case   ');
1
-----------
Space Case	--- This is the string 'Space Case'

splice> VALUES TRIM(BOTH ' ' FROM '      Space Case   ');
1
-----------
Space Case	--- This is the string 'Space Case'

splice> VALUES TRIM(TRAILING ' ' FROM '     Space Case     ');
1
-----------
     Space Case	--- This is the string '     Space Case'

splice> VALUES TRIM(CAST NULL AS CHAR(1) FROM '     Space Case     ');
1
-----------
NULL

splice> VALUES TRIM('o' FROM 'VooDoo');
1
----------
VooD

   -- results in an error because trimCharacter can only be 1 character
splice> VALUES TRIM('Do' FROM 'VooDoo');

See Also