Show pagesource

Old revisions

This is an old revision of the document!

QuickLinks	Statements	Functions	OdxSQL

Omnidex SQL Function: TRIM

Description

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 C STRING datatype.

Syntax

TRIM( character_expression )

TRIM( <LEADING | TRAILING | BOTH> ['character'] FROM character_expression )

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.

LEADING | TRAILING | BOTH

Optional. Specifies if the leading, trailing or both leading and trailing pad characters will be removed from a string. BOTH is the default.

'character'

Optional. Specify the pad character that is to be removed. If omitted, spaces will be removed.

Examples

Example 1: Column

> 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

Example 2: Column with LEADING

> 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

Example 3: Complex expression

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