TRIM function
Derby Reference Manual
143
1
--------------------------
1998-12-25 17:12:30.0
TRIM function
TRIM is a 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
}
trimType ::= { LEADING | TRAILING | BOTH }
trimCharacter ::= CharacterExpression
trimSource ::= CharacterExpression
If trimType is not specified, it will default 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, or.
· 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 trimChar removed.
· If trimType is TRAILING, the result will be the trimSource value with all trailing
occurrences of trimChar removed.
· If trimType is BOTH, the result will be the trimSource value with all leading *and*
trailing occurrences of trimChar 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
