Oracle: Example of TRIM function

Product: Oracle Database
Version: Any

TRIM function is used to remove white space from string, including
1. Both left and right spaces - default, or specify keyword BOTH
2. Left spaces - If specify keyword LEADING
3. Right spaces - If specify keyword TRAILING
4. Specific character in left and right - If specify keyword '[1 char]' FROM
5. Specific character in left - If specify keyword LEADING '[1 char]' FROM
6. Specific character in right - If specify keyword TRAILING '[1 char]' FROM

Example,

Table BONUS contain a single VARCHAR2 column, called ENAME

It contains 2 records as below:
1. [space][space]Col 1[space][space]
2. ..Col 1a.

Example 1: Remove white space from both left and right side
select TRIM (ENAME) ENAME from BONUS;


ENAME
----------
Col 1
..Col 1a.



Note: Row 2 no change


Example 2: Remove left spaces

select TRIM (LEADING FROM ENAME) ENAME from BONUS;



ENAME
----------
Col 1[space][space]
..Col 1a.

Note: Row 2 no change


Example 3: Remove right spaces
select TRIM (TRAILING FROM ENAME) ENAME from BONUS;


ENAME
----------
[space][space]Col 1
..Col 1a.



Note: Row 2 no change

Example 4:  Remove DOT in left and right


select TRIM ('.' FROM ENAME) ENAME from BONUS;


ENAME
----------
[space][space]Col 1[space][space]
Col 1a

Note: Row 1 no change




Example 5: Remove DOT in left

select TRIM (LEADING '.' FROM ENAME) ENAME from BONUS;





ENAME
----------
[space][space]Col 1[space][space]
Col 1a.

Note: Row 1 no change, and for row 2, the dot on left side removed

Example 6: Remove DOT in right

select TRIM (TRAILING '.' FROM ENAME) ENAME from BONUS;


ENAME
----------
[space][space]Col 1[space][space]
..Col 1a

Note: Row 1 no change, and for row 2, the dot on right side removed

Example 7: Remove space and DOT on both side

select TRIM(TRIM ('.' FROM ENAME)) ENAME from BONUS;

ENAME

----------

Col 1

Col 1a

Note: Both space and DOT characters are removed in both rows

These can be used while writing PL/SQL when assigning to variables like below

str_ENAME := TRIM(TRIM ('.' FROM str_ENAME));