The TRIM function removes any leading and/or trailing pad characters from a string. The pad character is a single character and defaults to a space character.
This function returns a STRING datatype.
TRIM( character_expression )
TRIM( <LEADING | TRAILING | BOTH> ['character'] FROM character_expression )
Required. Any expression that returns a character-class datatype. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype.
Optional. Specifies if the leading, trailing or both leading and trailing pad characters will be removed from a string. BOTH is the default.
Optional. Specify the pad character that is to be removed. If omitted, spaces will be removed.
> select trim(name) from individuals; TRIM(INDIVIDUALS.NAME) -------------------------------------------------- MS. MARY LEWIS MRS. ALICE R TOLLIVER MR. SIDNEY K TOLLIVER MRS. JENNIFER J HOPKINS MR. CHARLIE HOPKINS MS. KIMBERLY BLUE KATHLEEN TUCKER
> select individual, trim(leading '0' from individual) ind from individuals; INDIVIDUAL IND ------------ ------------ 000900011022 900011022 000900047761 900047761 000900047762 900047762 000900067115 900067115 000900067114 900067114 000900215567 900215567 000900240828 900240828
The following complex example creates a string containing the name and an optional email.
> Select (((trim($proper(NAME)) || >> case >> when EMAIL > ' ' then ' (' >> else '' >> end) || >> trim(EMAIL)) || >> case >> when EMAIL > ' ' then ')' >> else '' >> end) name_and_email >> from INDIVIDUALS; NAME_AND_EMAIL ----------------------------------------------------------------------------- Ms. Mary Lewis Mrs. Alice R Tolliver (alicetolliver@securitasinc.com) Mr. Sidney K Tolliver (sidneytolliver@deere.com) Mrs. Jennifer J Hopkins Mr. Charlie Hopkins Ms. Kimberly Blue Kathleen Tucker
See also: