• TRIM is a single row character function.
• TRIM enables you to trim leading or trailing characters (or both) from a character string.
• The function returns a value with datatype VARCHAR2.

Syntax : TRIM( [LEADING|TRAILING|BOTH] trim-expression FROM source-string)

• If you specify LEADING, then Oracle Database removes all leading characters specified in “trim-expression“. This is same as LTRIM.

SELECT TRIM(LEADING '#' FROM '###Trim Example#####') Result
  FROM DUAL;

RESULT
-----------------
Trim Example#####

• If you specify TRAILING, then Oracle Database removes all ending characters specified in “trim-expression“. This is same as RTRIM.

SELECT TRIM(TRAILING '#' FROM '###Trim Example#####') Result
  FROM DUAL;

RESULT
---------------
###Trim Example

• If you specify BOTH, then Oracle Database removes all leading and trailing characters specified in “trim-expression“. This is combination of both LTRIM and RTRIM.

SELECT TRIM(BOTH '#' FROM '###Trim Example#####') Result
  FROM DUAL;

RESULT
------------
Trim Example

• LEADING|TRAILING|BOTH are all optional options. If you do not specify any of the options, default is BOTH.

SELECT TRIM('#' FROM '###Trim Example#####') Result
  FROM DUAL;

RESULT
------------
Trim Example

• If you omit “trim-expression“, then the default value is a blank space. In this case specifying only “source-string” in TRIM function removes all leading and trailing blank spaces.

SELECT TRIM(' ### Trim Example# ####   ') Result
  FROM DUAL;

RESULT
----------------------
### Trim Example# ####

Difference between TRIM, LTRIM and RTRIM

• “trim-expression” can not contain more than 1 character in TRIM function. This is the difference between TRIM from LTRIM/RTRIM.
• If you want to trim multiple characters at a time, use LTRIM/RTRIM instead.

SELECT TRIM('51%$# ' FROM '%5111$ ### Trim Example# ####   $') Result
  FROM DUAL;
SELECT TRIM('51%$# ' FROM '%5111$ ### Trim Example# ####   $') Result
       *
ERROR at line 1:
ORA-30001: trim set should have only one character

SELECT RTRIM(LTRIM('%5111$ ### Trim Example# ####   $', '51%$# '), '$# ') Result
  FROM DUAL;

RESULT
------------
Trim Example