How To Use Oracle TRIM Function
  Home
 

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;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support computer help computer technician computer problems computer oracle trim oracle trim in oracle sql database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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.



See Also:
Online Tech Support Home Oracle Select Oracle Replace

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*