René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

trim, ltrim and rtrim [Oracle SQL]

trim ( string-to-be-trimmed );
trim ( trim-char from string-to-be-trimmed );
trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed );

ltrim ( string-to-be-trimmed [, trimming-char-set ] );

rtrim ( string-to-be-trimmed [, trimming-char-set ] );

Examples

#1

select '>'                                            || 
       trim ('   removing spaces at both sides     ') ||
       '<'  "Spaces removed"
  from dual;

Spaces removed
-------------------------------
>removing spaces at both sides<

#2

Same thing, but with the explicit both keyword and the implicit space as trim-char: 
select '>'                                                      || 
       trim (both from '   removing spaces at both sides     ') ||
       '<'  "Spaces removed"
  from dual;


Spaces removed
-------------------------------
>removing spaces at both sides<

#3

Almost the same thing as #2, but explicitely stating the '*' as trim-char: 
select '>'                                                       || 
       trim (both '*' from '***removing stars at both sides***') ||
       '<'  "Stars removed"
  from dual;

Stars removed
------------------------------
>removing stars at both sides<

#4

Removing leading characters only. Again, ' ' is implicitely assumed in absence of trim-char: 
select '>'                                                 || 
       trim (leading from '   removing leading spaces   ') ||
       '<'  "Leading spaces removed"
  from dual;

Leading spaces removed
----------------------------
>removing leading spaces   <

#5

Same thing as #4, but with ltrim: 
select '>'                                     || 
       ltrim ('   removing leading spaces   ') ||
       '<'  "Leading spaces removed"
  from dual;

Leading spaces removed
----------------------------
>removing leading spaces   <

#6

If trimming-char-set is specified, all characters of trimming-char-set are removed until the first character not belonging to trimming-char-set. Therefore, the ? after removing and the ! after trailing as well as the space after punctuation are not removed: 
select '>'                                                            || 
       rtrim ('   removing ? trailing ! punctuation !?!!??..', '!?.') ||
       '<'  "Trailing punctuation removed"
  from dual;

Trailing punctuation removed
---------------------------------------
>   removing ? trailing ! punctuation <