Oracle PL/SQL Programming, Third Edition

Name

TRIM

Synopsis

Oracle added the TRIM function in the Oracle8i release to increase compliance with the ANSI SQL standard. TRIM combines the functionality of RTRIM and LTRIM into one function. TRIM is a bit different from other SQL functions in that it allows the use of keywords where you would normally expect arguments. Well, what do you expect from a function designed by a committee?

TRIM is unusual in the same manner as TRANSLATE...USING, so again we provide syntax rather than a specification:

TRIM ([LEADING | TRAILING | BOTH] [trim_character] FROM trim_source)

where:

trim_source

Is the string you wish to trim.

trim_character

Specifies the character you wish to remove from one or both ends of the string. The default is to trim spaces.

LEADING | TRAILING | BOTH

Indicates whether you wish to trim from the beginning of the string (LEADING), from the end of the string (TRAILING), or both (BOTH). The default is to trim from both ends.

Following are some examples of TRIM:

• Remove leading and trailing spaces from a string:

  TRIM( '    Brighten the corner where you are.    '); 
   -->  'Brighten the corner where you are.'

• Remove only leading spaces:

  x := '    Brighten the corner where you are.    ';
  TRIM (LEADING FROM x)
       --> 'Brighten the corner where you are.    '

• Remove trailing periods:

  x := 'Brighten the corner where you are.';
  y := '.';
  TRIM (TRAILING y FROM x)
       --> 'Brighten the corner where you are'

Given that TRIM is ANSI-standard, is there any reason to ever again use RTRIM or LTRIM? It turns out that there ...