How To Use TRIM In Oracle

This tutorial page is based on examples to make it easier to follow. Function Oracle TRIM will remove (or trim) characters from the beginning and/or from the end of a text. The Oracle trim will remove by default only the white spaces from the text. The syntax of Oracle Trim is following:

TRIM(<your text>)

The first Oracle DBA example with Oracle Trim will show how works the trim function and to do so we will need to use Oracle Length function. You will be able to see the difference in the string length after the white spaces are removed and through that the string will become shorter.

 SELECT ' AAA ' AS original_text, 
         LENGTH (' AAA ') original_text_length, 
         trim (' AAA ') AS trimmed_text, 
         LENGTH (trim (' AAA ')) trimmed_text_length
   FROM dual;

Oracle Trim also allows to remove characters and there are 3 trimming options available. You can find those three syntax as following. The first following syntax will allow to remove the characters from the beginning and the additional key word is LEADING. Take a look at the syntax below:

TRIM(LEADING <char to be removed>  FROM <your text>)

The second example shows how to remove #-characters from the beginning and as you see from the output below the #-character has left to the end of text untouched.

 SELECT '#AAA#' AS original_text,
        trim (LEADING '#' FROM '#AAA#') AS trim_leading
   FROM dual;

To remove your defined characters from the end use the keyword TRAILING and the syntax of Oracle Trim with TRAILING keyword is:

TRIM(TRAILING <char to be removed>  FROM <your text>)

The example with TRAILING keyword in Oracle Trim removes the #-character from the end of the text but leaves the beginning part unchanged. Take a look at the example below to see how works Trim with TRAILING keyword.

 SELECT '#AAA#' AS original_text,
        trim (TRAILING '#' FROM '#AAA#') AS trim_trailing
   FROM dual;

To remove your characters from the beginning and end use Oracle Trim with the BOTH keyword or it can also be left out because Trim applies by default the both sides. The syntax is:

TRIM([BOTH] <char to be removed>  FROM <your text>)

The next Oracle DBA example brings out Oracle Trim Both options with and without the keyword. Take a look at the following example the #-characters are removed from both sides.

 SELECT '#AAA#' AS original_text,
        trim (BOTH '#' FROM '#AAA#') AS trim_both,
        trim ('#' FROM '#AAA#') AS trim
   FROM dual;

The last Select statement will show all trim options together and the declared character is still #-character.

 SELECT '#AAA#' AS original_text, 
        trim (LEADING '#' FROM '#AAA#') AS trim_leading, 
        trim (TRAILING '#' FROM '#AAA#') AS trim_trailing, 
        trim (BOTH '#' FROM '#AAA#') AS trim_both,
        trim ('#' FROM '#AAA#') AS trim_both
   FROM dual;

See the output above in four columns. In addition, It is important to know that Oracle Trim is removing characters only from the beginning and end. It does leave the characters in the middle of the string as is. If you need to remove all define characters use the Oracle Replace function instead.