Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. Single Row Character Functions
  5. TRIM

TRIM

  • 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
Was this article helpful to you? Yes No

How can we help?