Oracle SQL - Function TRIM function

Introduction

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:

Demo

SQL>
SQL> SELECT TRIM ('  This string has leading and trailing spaces          ')
  2  FROM dual-- w  ww .j av 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.

Demo

SQL>
SQL> SELECT TRIM (both ' ' from '           String with blanks        ')
  2  FROM dual;-- from  ww  w  .ja v  a2  s. co m

TRIM(BOTH''FROM'ST
------------------
String with blanks

SQL>

In these examples, characters rather than spaces are trimmed:

Demo

SQL>
SQL> SELECT TRIM('F' from 'Friday is ending with F')
  2  FROM dual;-- ww  w .j  av 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:

Demo

SQL>
SQL> SELECT TRIM(TRIM (both 'z' from 'zzzzz I am asleep zzzzzz'))
  2  FROM dual;--   ww  w.j  a v a  2 s . c o  m

TRIM(TRIM(B
-----------
I am asleep

SQL>

Related Topics