Oracle TRIM() Function

Oracle TRIM() is a built-in function that returns a string with any specified leading and trailing characters (by default, whitespace) removed.

Use LTRIM() to remove characters from the left side of a string, and RTRIM() to remove characters from the right side of a string.

Oracle TRIM() Syntax

Here is the syntax for the Oracle TRIM() function:

TRIM(str)
TRIM(trim_character FROM str)
TRIM({BOTH | LEADING | TRAILING} trim_character FROM str)

Here:

• BOTH means to remove both leading and trailing characters.
• LEADING means to remove only leading characters.
• TRAILING means to remove only trailing characters.
• If {BOTH | LEADING | TRAILING} is not specified, the default is BOTH.

Parameters

str

Required. The string to be processed. It can be a value of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

trim_character

Optional. The prefix and suffix characters to remove. You can specify only one character. If trim_character is not specified, the default is a single space.

Return Value

The Oracle TRIM() function returns a string with any specified leading and trailing characters (by default, whitespace) removed.

If any of the arguments are NULL, TRIM() returns NULL.

Oracle TRIM() Examples

Here are several examples of using the Oracle TRIM() function.

Removing Whitespace

This statement shows how to use the Oracle TRIM() function to remove whitespace from both sides of ' Hello ':

SELECT
    '   Hello   ' Origin,
    TRIM('   Hello   ') Result
FROM dual;

Output:

ORIGIN         RESULT
______________ _________
   Hello       Hello

Removing Specified Characters

This statement shows how to use the Oracle TRIM() function to remove the _*# characters from both sides of '___Hello___':

SELECT
    '___Hello___' Origin,
    TRIM('_' FROM '___Hello___') Result
    TRIM('_' FROM '___Hello___') Result
FROM dual;

Output:

ORIGIN         RESULT
______________ _________
___Hello___    Hello

BOTH vs LEADING vs TRAILING

This statement shows how BOTH, LEADING, and TRAILING work:

SELECT
    '___Hello___' Origin,
    TRIM(BOTH '_' FROM '___Hello___') BOTH,
    TRIM(LEADING '_' FROM '___Hello___') LEADING,
    TRIM(TRAILING '_' FROM '___Hello___') TRAILING
FROM dual;

Output:

ORIGIN         BOTH     LEADING     TRAILING
______________ ________ ___________ ___________
___Hello___    Hello    Hello___    ___Hello

NULL Arguments

If any of the arguments are NULL, TRIM() returns NULL.

SET NULL 'NULL';
SELECT
    TRIM(NULL) Result1,
    TRIM(NULL FROM 'ABC') Result2,
    TRIM('ABC' FROM NULL) Result3
FROM dual;

Output:

RESULT1    RESULT2    RESULT3
__________ __________ __________
NULL       NULL       NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle TRIM() is a built-in function that returns a string with any specified leading and trailing characters (by default, whitespace) removed.