How To Use Oracle's TRIM, LTRIM and RTRIM Functions

In this tip, I'll explain the three different TRIM functions in Oracle - TRIM, LTRIM, and RTRIM.

What is the TRIM Function?

The Oracle TRIM function allows you to remove characters from the left, right, or both sides of a string.

By default, it trims from both sides of a string, but you can specify which side.

The TRIM function looks like this:

TRIM (trim_string FROM source_string)

You can trim the characters from the left of the string (LEADING) or the right of the string (TRAILING) using extra keywords:

TRIM (LEADING trim_string FROM source_string)

An example of the TRIM function is:

SELECT TRIM('a' FROM 'anaconda') FROM dual;

Result:

nacond

What is the LTRIM Function?

The LTRIM function allows you to trim characters from the left of a string.

The LTRIM function looks like this:

LTRIM(source_string, trim_string)

An example of the LTRIM function is:

SELECT LTRIM('anaconda', 'a') FROM dual;

Result:

naconda

As you can see, it only trims the left 'a' and not the right one.

What is the RTRIM Function?

The RTRIM function trims characters from the right of the string. It's very similar to the LTRIM function.

The function looks like this:

RTRIM(source_string, trim_string)

An example of the RTRIM function is:

SELECT RTRIM('anaconda', 'a') FROM dual;

Result:

anacond

As you can see, it trims the right 'a' but not the left one.

What are the Differences?

So, it looks like these three functions are very similar.

What's different about them?

First of all, LTRIM and RTRIM allow you to trim multiple characters, and TRIM does not.

If you specify multiple characters in RTRIM or LTRIM, they will be checked individually against the source string and removed. They won't be checked as a single string.

For example:

SELECT
LTRIM('anaconda', 'an'),
LTRIM('anaconda', 'na')
FROM dual;

Result: Both functions return "aconda".

This is because the characters "a" and "n" are both checked individually, not the text value of "an".

Running the TRIM function with multiple characters will give an error.

SELECT
TRIM ('an' FROM 'anaconda')
FROM dual;

ORA-30001: trim set should have only one character.

*Cause: Trim set contains more or less than 1 character. This is not allowed in TRIM function.

Is there a Performance Difference?

In short, no. Several experiments, including this one on Stack Overflow, have shown that there is a very small difference in performance and not anything to encourage us to use one function over another.

So, use whichever function meets your requirements.