Oracle TRIM, LTRIM & RTRIM Function Guide, FAQ & Examples

In this article, you’ll learn all about the three TRIM functions in Oracle: TRIM, LTRIM, and RTRIM.

 

Purpose of the TRIM, LTRIM, and RTRIM Functions

The Oracle TRIM function will remove characters from the start or the end of a supplied string. It’s often used to remove space characters, such as where users enter an extra space that is not needed.

The Oracle LTRIM function will remove a specified character from the left side of a string. The L in LTRIM stands for “Left”, and is the opposite of the RTRIM or “Right” Trim function

Finally, the Oracle RTRIM function removes a specified character from the right side of a string.

This is most often used when you want to remove spaces from the right side of the string. However, it can be used for other characters as well, depending on the data.

Let’s take a look at the syntax of these three functions.

 

Syntax and Parameters

TRIM Syntax and Parameters

The syntax of the Oracle TRIM function is:

TRIM ( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] trim_source )

You can optionally specify one of LEADING, TRAILING or BOTH for this function. You can also specify the trim_character, but if you do, you need the FROM word.

The parameters of the TRIM function are:

• LEADING (optional): If you specify LEADING, the function removes the trim_character from the beginning of the trim_source string.
• TRAILING (optional): If you specify TRAILING, the function removes the trim_character from the end of the trim_source string.
• BOTH (optional): If you specify BOTH, the function removes the trim_character from the beginning and the end of the trim_source string.
• trim_character FROM (optional): This is the character to remove from the trim_source string. If this is not specified, then a space is used. If it is a literal value, it needs to be in single quotes.
• trim_source (mandatory): This is the string value that characters are removed from.

Some additional points to remember for this function:

• If you don’t specify LEADING, TRAILING, or BOTH, the function performs the same action as though you specified BOTH. As in, it removes characters from the beginning and the end of the string.
• Both trim_character and trim_source can be any of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• The function returns a VARCHAR2 data type if the trim_source is a character data type, or LOB if the trim_source is a LOB data type. It is also returned in the same character set as trim_source.
• You can use the CHR function as the trim_character if needed. For example, using CHR(100).

 

LTRIM Syntax and Parameters

The LTRIM function syntax is:

LTRIM ( input_string, [trim_string] )

The parameters of the LTRIM function are:

• input_string (mandatory): This is the string to remove characters from the left-hand side of.
• trim_string (optional): This is the string to be removed from the input_string. If it is not specified, a space is used, and all spaces are removed from the left of the input_string.

Some points to note about LTRIM:

• If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
• Both input_string and trim_string can be of data type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• The returned value is a VARCHAR2 data type if the input types are CHAR, VARCHAR2, NCHAR or NVARCHAR2, and the returned value is a LOB data type if the input types are LOB or CLOB.
• The trim_string can be more than one character
• The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

 

RTRIM Syntax and Parameters

The syntax for the Oracle RTRIM function is:

RTRIM ( input_string, [trim_string] )

The parameters of the RTRIM function are:

• input_string (mandatory): This is the string that will have the characters trimmed from it.
• trim_string (optional): This is the value to trim or remove from the input_string. If it is omitted, a space character is used for trimming.

The R in RTRIM stands for Right, because it removes the characters from the right of the string.

Some other points to remember:

• If the trim_string is a literal value, you need to include it inside single quotes. For example, to remove an underscore, you need to specify it as ‘_’
• Both parameters can be any of the types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• The string returned is a VARCHAR2 data type if the input_string was a character, and LOB if it was a LOB data type.
• The trim_string can be more than one character
• The function removes each individual value inside trim_string, not the string as a whole. See the Examples section below for more information.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

Can Oracle Trim the Last Character?

Yes, this is possible with the TRIM function, but it’s probably better to use the SUBSTR function.

The TRIM function needs you to specify which character to remove, and it removes it from the end of the string. A SUBSTR function can be written to remove the last character from a string.

See some examples of the SUBSTR function here, including an explanation of the SUBSTR function.

 

Can TRIM Be Used To Trim Spaces from a String?

Yes, and this is probably the most common usage of the Oracle TRIM function.

I’ve used it many times to clean up user input (for example, if users enter their name with a space at the end of it). It’s better for storing in the database, and helpful for doing data comparisons.

To do this, your function could be:

TRIM(trim_value)

This will return the trim_value without any spaces at the end or the beginning, which effectively trims spaces from a string. See the examples below for more on how this is done.

 

How Can Oracle Trim Newline Characters Or Trim a Carriage Return?

Using the TRIM function to remove newline characters or a carriage return can be done with Oracle. This can work if you only have a newline character or only a carriage return, as you can use the CHR function.

TRIM(CHR(10), your_string)

Will remove new line characters.

TRIM(CHR(13), your_string)

Will remove carriage return characters.

CHR(10) returns a new line character and CHR(13) returns a carriage return character.

But what if you have both? Or it is mixed with spaces and tab characters (CHR(9))?

The main issue is that TRIM only removes one type of character – you can only specify a single character for TRIM to remove.

What if you use a nested TRIM function?

TRIM (CHR(10), TRIM(CHR(13), your_string))

This can work, but it could be slow, especially if you have it inside a loop or a large query.

An approach recommended by users at Stack Overflow is the TRANSLATE function, to translate all occurrences of a string into a space, and then TRIM the string of spaces. See the examples below on how to do this.

You can also use a REGEXP_REPLACE function, which is a bit more flexible but more complicated.

 

Can Oracle Trim The Left Of The String?

Yes, this is done by specifying LEADING in the function parameter. You need to specify a character, so it might be something like this:

TRIM(LEADING ' ' FROM your_string)

This will use the Oracle TRIM function to trim the left of the string.

Alternatively, you can use the LTRIM function.

LTRIM(your_string, ‘ ‘)

 

How Can Oracle Trim All Whitespace From A String?

You can use the TRIM function to remove the spaces from the beginning and end of a string.

If you want to include newline and carriage return characters, use the TRANSLATE function as well, as shown in the example below.

You can also use a REGEXP_REPLACE function, which is a bit more flexible but more complicated.

 

How Can Oracle Trim Spaces Between Words?

The Oracle TRIM function does not trim spaces between words. You can use a regular expression with REGEXP_REPLACE to remove occurrences of more than once space. Or, you can use the REPLACE function to remove all spaces between words – but this would result in a single long word.

 

How Can Oracle Trim a Column?

You can use Oracle TRIM on a column name instead of a specific string. You would just specify the column name, as well as your parameters.

If you want to update the values in the column, you would use an UPDATE statement:

UPDATE your_table
SET your_column = TRIM(your_column)
WHERE [conditions];

 

Can You Use Oracle LTRIM To Remove Leading Zeroes?

Yes, you can. It’s one of the more common uses for the function that I’ve seen.

This can be done as:

LTRIM(value, ‘0’)

See the Examples section below for more information.

 

Can You Use Oracle LTRIM with RTRIM?

Yes, you can, and it works in the same way as just using the TRIM function.

You’ll need to use one inside the other, and it doesn’t really matter which one is used first.

So, you can use either LTRIM(RTRIM(value)) or RTRIM(LTRIM(value)).

See the Examples section below for more information.

 

Are There Other Ways for Oracle to Trim Strings?

Yes, there are a few ways you can trim strings in Oracle:

• Use LTRIM or RTRIM
• Use TRIM
• Use REPLACE
• Use SUBSTR if you need more advanced trimming features
• Use regular expressions

 

Can You Use Oracle LTRIM or RTRIM with Special Characters?

Yes, you can. You can use exclamation points and many kinds of special characters as input.

You can also use Unicode characters, such as å, as the function supports NVARCHAR2 types.

See the Examples section for more information.

 

Can You Use Oracle LTRIM or RTRIM with CHAR or LOB Types?

Yes, the Oracle RTRIM can handle CHAR and LOB types. It can handle CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types.

 

Examples of the TRIM Function

Here are some examples of the TRIM function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1 – Trailing Spaces

This example removes the trailing spaces from a string.

SELECT TRIM('Jack   ')
FROM dual;

Result:

Jack

The spaces at the end of “Jack   ” are removed.

 

Example 2 – Leading Spaces

This example removes the leading spaces from a string.

SELECT TRIM(' John')
FROM dual;

Result:

John

The spaces at the start of ” John” are removed.

 

Example 3 – Leading and Trailing

This example removes the leading and trailing spaces from a string.

SELECT TRIM(' Julie   ')
FROM dual;

Result:

Julie

The spaces at the start and end of ”  Julie  ” are removed.

 

Example 4 – LEADING Keyword

This example removes the leading spaces from a string by specifying LEADING.

SELECT TRIM(LEADING ' ' FROM ' Julie   ')
FROM dual;

Result:

Julie

The spaces at the start of ”  Julie  ” are removed, but the spaces at the end remain. You might not be able to see it on the page here unless you highlight the text.

 

Example 5 – TRAILING Keyword

This example removes the trailing spaces from a string by specifying TRAILING.

SELECT TRIM(TRAILING ' ' FROM ' Julie   ')
FROM dual;

Result:

 Julie

The spaces at the end of ”  Julie  ” are removed, but the spaces at the start remain. You might not be able to see it on the page here unless you highlight the text.

 

Example 6 – BOTH Keyword

This example removes the leading and trailing spaces from a string by specifying BOTH.

SELECT TRIM(BOTH ' ' FROM ' Julie   ')
FROM dual;

Result:

Julie

The spaces at the start and end of ”  Julie  ” are removed.

 

Example 7 – Underscore

This example removes an underscore from the provided string.

SELECT TRIM('_' FROM 'Jordan___')
FROM dual;

Result:

Jordan

 

Example 8 – Newline and Carriage Return

This example converts the newline, carriage return, and tab characters to spaces and removes them from a string.

SELECT
'first line' || CHR(9) || 'after tab' || CHR(10) || 'next line'  || CHR(13) ||  'another line',
TRIM(
  TRANSLATE(
    TRANSLATE(
      TRANSLATE(
        'first line' || CHR(9) || 'after tab' || CHR(10) || 'next line'  || CHR(13) ||  'another line', CHR(10), ' '
      )
      , CHR(13), ' ')
    , CHR(09), ' ')
  ) AS trimmed
FROM dual;

Result:

FULL_STRING	TRIMMED
first line after tabnext line another line	first line after tab next line another line

The newline, carriage return, and tab characters are converted to spaces, and the entire result is then trimmed to ensure there is no spaces either side of the string, which is a possibility if there is a new line at the end of a string.

 

Example 9 – Update Statement

This example updates a value in the customers table.

First, let’s see what is in the customers table.

SELECT first_name
FROM customers
WHERE customer_id = 123;

Result:

FIRST_NAME

Jason

Now, let’s update the first_name to remove spaces

UPDATE customers
SET first_name = TRIM(first_name)
WHERE customer_id = 123;

Now, let’s have another look at the customers table.

SELECT first_name
FROM customers
WHERE customer_id = 123;

Result:

FIRST_NAME

Jason

The space has been removed from this value.  You might not be able to see it on the page here unless you highlight the text.

 

Example 10 – Simple LTRIM and RTRIM

This example demonstrates a simple LTRIM and RTRIM with no trim value specified.

SELECT LTRIM(' Database Star') AS ltrim_example,
RTRIM('Database Star   ') AS rtrim_example
FROM DUAL;

Result:

LTRIM_EXAMPLE	RTRIM_EXAMPLE
Database Star	Database Star

The extra spaces are removed from the original value.

 

Example 11 – LTRIM and RTRIM with Specific Value

This example uses a specific value to trim.

SELECT LTRIM('___Database Star', '_') AS ltrim_example,
RTRIM('Database Star__', '_') AS rtrim_example,
FROM DUAL;

Result:

LTRIM_EXAMPLE	RTRIM_EXAMPLE
Database Star	Database Star

The underscores are removed from the original value.

 

Example 12 – LTRIM and RTRIM with Several Characters

This example uses LTRIM with several characters as the string to trim.

SELECT LTRIM('; ; ; ; ; Database Star', ' ; ') AS ltrim_example,
LTRIM('Database Star! ! ! ', '! ') AS rtrim_example
FROM DUAL;

Result:

LTRIM_EXAMPLE	RTRIM_EXAMPLE
Database Star	Database Star

Both the spaces and semicolons are removed from the original value.

 

Example 13 – LTRIM and RTRIM on Table Columns

This example uses LTRIM and RTRIM on data in a table, instead of providing a value.

SELECT country,
LTRIM(country, 'U’) AS ltrim_example,
RTRIM(country, 'A') AS rtrim_example
FROM customers;

Result:

COUNTRY	LTRIM_EXAMPLE	RTRIM_EXAMPLE
USA	SA	US
France	France	France
Canada	Canada	Canada
UK	K	UK
Norway	Norway	Norway

The capital U is removed from the left of several values, and the capital A is removed from the right of several values.

 

Example 14 – LTRIM and RTRIM Zeroes

This example uses LTRIM and RTRIM with 0 as the parameter.

SELECT LTRIM('000Database Star', 0) AS ltrim_example,
RTRIM('Database Star000', 0) AS rtrim_example
FROM dual;

Result:

LTRIM_EXAMPLE	RTRIM_EXAMPLE
Database Star	Database Star

The zeroes are removed from the original value.

 

Example 15 – LTRIM and RTRIM Combined

This example uses both LTRIM and RTRIM in the one expression.

SELECT LTRIM(RTRIM('___Database Star__', '_'), '_') AS ltrim_rtrim_example
FROM DUAL;

Result:

LTRIM_RTRIM_EXAMPLE

Database Star

The underscores are removed from both sides of the original value.

 

Example 16

This example uses Unicode characters as the trim parameter.

SELECT LTRIM('ééDatabase Star', 'é') AS ltrim_example,
RTRIM('Database Staréééé', 'é') AS rtrim_example
FROM DUAL;

Result:

LTRIM_EXAMPLE	RTRIM_EXAMPLE
Database Star	Database Star

The accented “e” character is removed from the original value.

 

Similar Functions

Some functions which are similar to the Oracle TRIM function are:

• REPLACE – lets you replace a character with a different character within a string.
• REGEXP_REPLACE – performs a similar function to REPLACE but uses regular expressions.
• TRANSLATE – similar to REPLACE but lets you perform several one-to-one replacements inside one function.

You can find a full list of Oracle functions here.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: