LTRIM, RTRIM, and TRIM

The trimming functions in Oracle frequently are used by many developers; but not all know the full set of options available and how they compare to each other. Sometimes I need to remind myself so this post is as much for me as for others. I hope you find it helpful.

The LTRIM and RTRIM functions both default to removing a blanks from the left (LTRIM) or right (RTRIM) ends of the input string if that is the only parameter used. If the a second parameter is passed, then all characters within that set, regardless of order will be trimmed from their corresponding sides. Frequently, I see code with a single character for the trim option, but rarely the second and it is both a convenient and efficient means of doing so. The following examples demonstrate the various syntax and functional variations.

SELECT '>' || result || '<' result
  FROM (SELECT LTRIM('    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT LTRIM('abc.......cba', 'a') result FROM DUAL
        UNION ALL
        SELECT LTRIM('abc.......cba', 'bac') result FROM DUAL
        UNION ALL
        SELECT RTRIM('    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT RTRIM('abc.......cba', 'a') result FROM DUAL
        UNION ALL
        SELECT RTRIM('abc.......cba', 'bac') result FROM DUAL);

RESULT
-------------------
>abc.......cba    <
>bc.......cba<
>.......cba<
>    abc.......cba<
>abc.......cb<
>abc.......<

A relatively common use I find for the multi-character functionality is cleaning up DOS end-of-line sequences of carriage-return and line-feed characters. For example:

RTRIM(some_variable,chr(13)||chr(10))

TRIM, in contrast to the LTRIM and RTRIM functions, only works with a single character; but still defaults to blanks if no character is specified.
Also, rather than using a simple positional parameter, the non-default functionality is specified with a descriptive clause preceding the input string.
A LEADING clause will produce results similar to LTRIM, a TRAILING clause similar to RTRIM – but specifying only a single character to be trimmed.

Using BOTH allows for trimming a non-default character from both sides. The keyword BOTH is optional though as seen in the last two subqueries in the examples below.

SELECT '>' || result || '<' result
  FROM (SELECT TRIM('    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT TRIM(LEADING ' ' FROM '    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT TRIM(TRAILING ' ' FROM '    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT TRIM(BOTH ' ' FROM '    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT TRIM(' ' FROM '    abc.......cba    ') result FROM DUAL
        UNION ALL
        SELECT TRIM('a' FROM 'abc.......cba') result FROM DUAL);

RESULT
-------------------
>abc.......cba<
>abc.......cba    <
>    abc.......cba<
>abc.......cba<
>abc.......cba<
>bc.......cb<

Finally, if you forget the single-character limit of TRIM, Oracle will remind you explicitly of the error.

SQL> SELECT TRIM('abc' FROM 'abc.......cba') result FROM DUAL;
SELECT TRIM('abc' FROM 'abc.......cba') result FROM DUAL
       *
ERROR at line 1:
ORA-30001: trim set should have only one character