в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:11:32Z

1 версия

Новая страница

== 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==

<OL><LI>TRIM removes characters from the left or right ends of a string or both ends.</LI><LI>LTRIM and RTRIM trim characters from the left or right ends of strings.</LI><LI>TRIM supercedes both of these.</LI></OL>
<p>The general syntax of TRIM is:
TRIM ([where] [trim character] FROM subject string)</p>
<OL><LI>The optional "where" is one of the keywords "leading," "trailing," or "both."</LI><LI>If the optional "trim character" is not present, then blanks will be trimmed.</LI><LI>"Trim character" may be any character.</LI><LI>The word FROM is necessary only if where or trim character is present.</LI></OL>



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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Character String Functions/TRIM - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия