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 asLTRIM
.
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 asRTRIM
.
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 bothLTRIM
andRTRIM
.
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 isBOTH
.
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 betweenTRIM
fromLTRIM
/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