Oracle PL/SQL Tutorial/Character String Functions/TRIM

Characters rather than spaces are trimmed

SQL> SELECT TRIM("F" from "ABCDFG")FROM dual;
TRIM("
------
ABCDFG
SQL>

match trimmed string

SQL>
SQL>
SQL> CREATE TABLE book(
  2    title_id   CHAR(3)      NOT NULL,
  3    title_name VARCHAR(40)  NOT NULL,
  4    type       VARCHAR(10)  NULL    ,
  5    pub_id     CHAR(3)      NOT NULL,
  6    pages      INTEGER      NULL    ,
  7    price      DECIMAL(5,2) NULL    ,
  8    sales      INTEGER      NULL    ,
  9    pubdate    DATE         NULL    ,
 10    contract   SMALLINT     NOT NULL
 11  );
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO book VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1);
1 row created.
SQL> INSERT INTO book VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0);
1 row created.
SQL> INSERT INTO book VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1);
1 row created.
SQL> INSERT INTO book VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1);
1 row created.
SQL> INSERT INTO book VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1);
1 row created.
SQL>
SQL>
SQL> SELECT title_id
  2    FROM book
  3    WHERE TRIM(title_id) LIKE "T1_";
TIT
---
T10
T11
T12
T13
SQL>
SQL>
SQL> drop table book;
Table dropped.
SQL>
SQL>

Nested trim

SQL> SELECT TRIM(TRIM (both "z" from "zzzzz I am asleep zzzzzz")) FROM dual
  2  ;
TRIM(TRIM(B
-----------
I am asleep
SQL>

The TRIM Function

1. TRIM removes characters from the left or right ends of a string or both ends.
2. LTRIM and RTRIM trim characters from the left or right ends of strings.
3. TRIM supercedes both of these.

The general syntax of TRIM is: TRIM ([where] [trim character] FROM subject string)

1. The optional "where" is one of the keywords "leading," "trailing," or "both."
2. If the optional "trim character" is not present, then blanks will be trimmed.
3. "Trim character" may be any character.
4. The word FROM is necessary only if where or trim character is present.

SQL> SELECT TRIM (" AAA       ") FROM dual;
TRI
---
AAA
SQL>

TRIM (both " " from " String with blanks ")

SQL> SELECT TRIM (both " " from " String with blanks ") FROM dual;
TRIM(BOTH""FROM"ST
------------------
String with blanks
SQL>

TRIM from both sides

SQL> SELECT TRIM (both "z" from "zzzzz I am asleep zzzzzz") FROM dual;
TRIM(BOTH"Z"F
-------------
 I am asleep
SQL>

Triming leading "H" from employee last name

SQL>
SQL>
SQL> CREATE TABLE employees (
  2    au_id    CHAR(3)     NOT NULL,
  3    au_fname VARCHAR(15) NOT NULL,
  4    au_lname VARCHAR(15) NOT NULL,
  5    phone    VARCHAR(12) NULL    ,
  6    address  VARCHAR(20) NULL    ,
  7    city     VARCHAR(15) NULL    ,
  8    state    CHAR(2)     NULL    ,
  9    zip      CHAR(5)     NULL
 10  );
Table created.
SQL>
SQL> INSERT INTO employees VALUES("A01","S","B","111-111-1111","75 St","Boston","NY","11111");
1 row created.
SQL> INSERT INTO employees VALUES("A02","W","H","222-222-2222","2922 Rd","Boston","CO","22222");
1 row created.
SQL> INSERT INTO employees VALUES("A03","H","H","333-333-3333","3800 Ave, #14F","San Francisco","CA","33333");
1 row created.
SQL> INSERT INTO employees VALUES("A04","K","H","444-444-4444","3800 Ave, #14F","San Francisco","CA","44444");
1 row created.
SQL> INSERT INTO employees VALUES("A05","C","K","555-555-5555","114 St","New York","NY","55555");
1 row created.
SQL> INSERT INTO employees VALUES("A06"," ","K","666-666-666","390 Mall","Palo Alto","CA","66666");
1 row created.
SQL> INSERT INTO employees VALUES("A07","P","O","777-777-7777","1442 St","Sarasota","FL","77777");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT au_lname, TRIM(LEADING "H" FROM au_lname) AS "Trimmed name"
  2    FROM employees;
AU_LNAME        Trimmed name
--------------- ---------------
B               B
H
H
H
K               K
K               K
O               O
7 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>

TRIM Leading and Trailing Zeroes

SQL> SELECT TRIM (0 FROM 067270676800) "TRIM Example"
  2  FROM DUAL;
TRIM Exam
---------
672706768
SQL>
SQL>

TRIM(leading "F" from "FABCDEF")

SQL> SELECT TRIM(leading "F" from "FABCDEF") FROM dual;
TRIM(L
------
ABCDEF
SQL>

TRIM(trailing "r" from "Real water")

SQL> SELECT TRIM(trailing "r" from "Real water")FROM dual;
TRIM(TRAI
---------
Real wate
SQL>