Omnidex SQL Function: TRIM

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 ¹⁾ ||

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 </code>