Oracle PL/SQL Tutorial/Character String Functions/TRIM
Содержание
- 1 Characters rather than spaces are trimmed
- 2 match trimmed string
- 3 Nested trim
- 4 The TRIM Function
- 5 TRIM (both " " from " String with blanks ")
- 6 TRIM from both sides
- 7 Triming leading "H" from employee last name
- 8 TRIM Leading and Trailing Zeroes
- 9 TRIM(leading "F" from "FABCDEF")
- 10 TRIM(trailing "r" from "Real water")
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
- TRIM removes characters from the left or right ends of a string or both ends.
- LTRIM and RTRIM trim characters from the left or right ends of strings.
- TRIM supercedes 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.
<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>