This is an old revision of the document!
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.
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