TRIM ORACLE - Online Tech Support

This page is based on examples to be easier to follow. Function Oracle Trim will remove or trim characters from your text beginnings and/or ends. The Trim in Oracle will by default remove white spaces from your text and it’s syntax is:

TRIM(<your text>)

The first example is showing how the TRIM is working using the Oracle LENGTH function and you can see below the output after the white spaces are removed the function returns the smaller string amount.

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

Also Oracle trim lets you to remove other characters and to do it there are 3 options. The first option is to remove the characters from the beginning of the text. The TRIM function can be used with an extra keyword named LEADING and that makes to trim only from the beginning part of a string. The syntax is following:

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

The example removes #-characters from the beginning and it leaves the # character ending part of the text as is.

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

To remove the selected characters from the end you can use the TRAILING keyword. The syntax with the TRIM with TRAILING is:

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

The following example is using the TRAILING keyword in Oracle TRIM and it does remove the #-character from the end of the text but it leaves the beginning part untouched.

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

To remove the defined character from the end and beginning use in Oracle TRIM the BOTH keyword or the second option is without any keyword at all. The both options working in the same way. The TRIM syntax with BOTH is:

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

The last example cleans the text from the #-characters and it contains both options for trimming.

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

It is important to know that Oracle Trim is removing characters only from the beginning and end and it does leave the characters in the middle of string. If you need to remove all define characters use the Oracle Replace function instead.