This tutorial explains how to use Oracle trim function with basic syntax and examples.

The Oracle TRIM function deletes space or specified characters from a string’s start, end, or both.

This article will cover the following subjects:

1. Oracle TRIM All Whitespace
2. Oracle TRIM Leading Zeros
3. Oracle TRIM Trailing Zeros
4. Oracle TRIM Both

Syntax:

TRIM( { { LEADING OR TRAILING OR BOTH } trim_character FROM } string)

Important Notes:

• If we do not specify a value for the first parameter, which is one of (LEADING, TRAILING, or BOTH), the TRIM function will remove trim_character from both the start and end of the string.
• If we do not specify trim_character, then oracle will consider a blank space.
• The TRIM function will return a value with the datatype VARCHAR2.
• The TRIM function will return null in case of trim_character & string are null.

Oracle trim All Whitespace

We can remove or omit all whitespaces from a string in Oracle using the oracle TRIM function without specifying any parameters to it, just passing the string with the spaces at the beginning and end.

Example (1):

SELECT TRIM (' Oraask.Com ') 
FROM dual;

Output:

Oraask.Com

Explanation:

In the above example, we use the trim function in oracle without specifying any keywords to remove all whitespace from the string “Oraask.Com“.

Oracle trim Leading Zeros

We can remove or omit all zeros from the start of a string in Oracle using the Oracle TRIM function by specifying the LEADING and FROM keywords.

Example (2):

SELECT TRIM (LEADING '0' FROM '00077.5') 
FROM dual;

Output:

77.5

Explanation:

In the above example, we used the trim function with a leading zero against the number “00077.5”. This will return the value “77.5” without the leading three zeros.

Oracle trim Trailing Zeros

If the string has zeros at the end, and we want to omit or remove all zeros at the end, we can call the trim function by specifying TRAILING and FROM keywords.

Example (3):

SELECT TRIM (TRAILING '0' FROM '77.530000') 
FROM dual;

Output:

77.53

Explanation:

In the above example, we used a trim function with trailing zero against the “77.53000”. This will return the value “77.53” without the trailing three zeros.

Oracle trim BOTH

We use BOTH and FROM keywords; if we want to omit or remove a specific character from both directions, start and end the string.

Example (4):

SELECT TRIM (BOTH '1' FROM '123Oraask111')
FROM dual;

Output:

23Oraask

Explanation:

In the above example, we used the trim function with both keywords specifying “1” character against the string “123Oraask111” to remove the “1” character from the string. This will return the value “23Oraask” without the “1” character. The BOTH keyword with trim function searches for the specified character, and wherever it the place in the string, the trim function removes the character.

Conclusion

In this article we explained how to use oracle trim function to remove whitespaces, leading, trailing zeros, and specific character from a string.

Similar Functions to Oracle TRIM:

• LENGTH : rounds the column, expression, or value to n decimal places.
• INSTR : returns the length of specified string.
• SUBSTR : extracts a sub-string from a string.
• Oracle || operator : Oracle || operator used to Concatenates character strings and CLOB data.