Tutorial 1 - The Oracle "Trim" Function

The trim function removes all specified characters either from the beginning or the ending of a string. This is very useful when you want to cleanup data that may have different column lengths and unusable or annoying characters or blank spaces.


An example would be a city. Some cities may be long like, "San Francisco" whereas others might be short. Because the column may include leading or trailing blank spaces it's a good idea to "trim" the field.


This function can also be useful to cleanup data for validation, some people may unintentionally add a blank space in a form field (i.e. " Andrew Krone" instead of just "Andrew Krone".


Ultimately, it just makes data look cleaner.


For example:

trim(' Ventura ') would return 'Ventura'
trim(' ' from ' tech ') would return 'tech'
trim(leading '0' from '000123') would return '123'
trim(trailing '1' from 'Tech1') would return 'Tech'
trim(both '1' from '123Tech111') would return '23Tech'

Applies to :

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

(thanks go to techonthenet.com)