Oracle PL/SQL Tutorial/Character String Functions/TRIM

Материал из SQL эксперт
Перейти к: навигация, поиск

Characters rather than spaces are trimmed

   <source lang="sql">

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


ABCDFG SQL></source>


match trimmed string

   <source lang="sql">

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></source>


Nested trim

   <source lang="sql">

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

 2  ;

TRIM(TRIM(B


I am asleep SQL></source>


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.



   <source lang="sql">

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


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

   <source lang="sql">

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


String with blanks SQL></source>


TRIM from both sides

   <source lang="sql">

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


I am asleep

SQL></source>


Triming leading "H" from employee last name

   <source lang="sql">

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></source>


TRIM Leading and Trailing Zeroes

   <source lang="sql">

SQL> SELECT TRIM (0 FROM 067270676800) "TRIM Example"

 2  FROM DUAL;

TRIM Exam


672706768 SQL> SQL></source>


TRIM(leading "F" from "FABCDEF")

   <source lang="sql">

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


ABCDEF SQL></source>


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

   <source lang="sql">

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


Real wate SQL></source>