TRIM function removes characters from the left or right ends of a string or both ends.
The TRIM function was added in Oracle 9.
Originally, LTRIM and RTRIM were used for trimming characters from the left or right ends of strings.
TRIM supersedes both of these.
The general syntax of TRIM is:
TRIM ([where] [trim character] FROM subject string)
The optional where is one of the keywords "leading," "trailing," or "both."
If the optional trim character is not present, then blanks will be trimmed.
Trim character may be any character.
The word FROM is necessary only if where or trim character is present. Here is an example:
SQL> SQL> SELECT TRIM (' This string has leading and trailing spaces ') 2 FROM dual-- from w ww .jav a 2 s . co m 3
Both the leading and trailing spaces are deleted.
You can use both and set the character to be removed.
SQL> SQL> SELECT TRIM (both ' ' from ' String with blanks ') 2 FROM dual;-- from ww w .ja va 2 s . c o m TRIM(BOTH''FROM'ST ------------------ String with blanks SQL>
In these examples, characters rather than spaces are trimmed:
SQL> SQL> SELECT TRIM('F' from 'Friday is ending with F') 2 FROM dual;-- from w w w . j a v a 2 s.c o m TRIM('F'FROM'FRIDAYIS --------------------- riday is ending with SQL> SQL> SELECT TRIM(leading 'F' from 'Friday F') 2 FROM dual; TRIM(LE ------- riday F SQL> SQL> SELECT TRIM(trailing 'r' from 'rain water') 2 FROM dual; TRIM(TRAI --------- rain wate SQL> SQL> SELECT TRIM (both 'z' from 'zzzzz I am asleep zzzzzz') 2 FROM dual; TRIM(BOTH'Z'F ------------- I am asleep SQL>
In the last example, the blank space was preserved because it was not trimmed.
To get rid of the leading/trailing blank(s) we can nest TRIMs like this:
SQL> SQL> SELECT TRIM(TRIM (both 'z' from 'zzzzz I am asleep zzzzzz')) 2 FROM dual;-- w ww .j a v a2 s. co m TRIM(TRIM(B ----------- I am asleep SQL>