FUNCTION TRIM ( [LEADING | TRAILING | BOTH] trim_chars FROM trim_source ) RETURN VARCHAR2;
where trim_chars is the number of characters to trim from trim_source .
This function conforms to these rules:
The following block exercises the various forms of the TRIM command:
/* Filename on companion disk: trim.sql */ DECLARE mystr VARCHAR2(10); yourstr VARCHAR2(10) := 'Toronto'; BEGIN DBMS_OUTPUT.PUT_LINE ( TRIM (LEADING 'x' FROM 'xyzabc')); DBMS_OUTPUT.PUT_LINE ( TRIM (TRAILING 'cb' FROM 'xyzabc')); DBMS_OUTPUT.PUT_LINE ( TRIM (BOTH 'x1' FROM '1x1yzabc111x')); DBMS_OUTPUT.PUT_LINE ( TRIM ('x1' FROM '1x1yzabc111x')); DBMS_OUTPUT.PUT_LINE ( TRIM (mystr FROM yourstr)); END; /
and results in this output:
yzabc xyza yzabc yzabc Toronto
The CAST function converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value. CAST was first made available in Oracle 8.0 and has been extended in a variety of ways in Oracle8 i. CAST, in essence, offers a single program name (and different syntax) to replace a variety of conversion programs, such as ROWIDTOHEX, TO_DATE, TO_CHAR, and so on. Here is the general syntax of the CAST statement:
CAST ( expression AS type )
where expression is the expression to be converted and type is the name of the datatype to which the expression is converted.
You can cast an unnamed expression (such as a date or the result set of a subquery) or a named collection (such as a VARRAY or a nested table) into a type-compatible datatype or named collection. The type must be the name of a built-in datatype or collection type and the expression must be a built-in datatype or must evaluate to a collection value.
The expression can be either a built-in datatype or a collection type, and a subquery must return a single value of the collection type or the built-in type. If you are casting the result set of a subquery, you must then specify the MULTISET keyword to tell Oracle to return a collection value. MULTISET is illustrated in the last example in Section 10.3.2.2, "Collection examples .
Table 10.1 shows which built-in datatypes can be cast into which other built-in datatypes.
Note the following about using CAST:
Let's take a look at some examples (you will find all of these grouped together in the cast.sql file on the disk).
You can use CAST instead of other datatype conversion functions:
/* Filename on companion disk: cast.sql */ BEGIN /* CAST for TO_DATE...no way to pass format mask. Instead, the database default or session setting of NLS_DATE_FORMAT is used. */ DBMS_OUTPUT.PUT_LINE (
As noted, there isn't any way to include a format mask with which to drive the conversion. If I use a different format for my date string, I get an error:
SQL> 1 BEGIN 2 DBMS_OUTPUT.PUT_LINE ( 3 CAST ('1/1/99' AS DATE)); 4* END; 5 / ERROR at line 1: ORA-01843: not a valid month
I can use CAST to convert a ROWID to a string as follows:
SQL> SELECT CAST (ROWID AS VARCHAR2(20)) Vrowid FROM department; VROWID -------------------- AAADIKAACAAAAMeAAA AAADIKAACAAAAMeAAB AAADIKAACAAAAMeAAC AAADIKAACAAAAMeAAD
Yet I cannot run this same query inside PL/SQL:
SQL> DECLARE 2 my_rowid VARCHAR2(20); 3 BEGIN 4 FOR rec IN ( 5 SELECT CAST (ROWID AS VARCHAR2(20)) Vrowid 6 FROM department) 7 LOOP 8 DBMS_OUTPUT.PUT_LINE (rec.Vrowid); 9 END LOOP; 10 END; 11 / ERROR at line 1: PLS-00220: simple name required in this context
So I am not sure how useful CAST will ever be for PL/SQL developers for conversion of scalar datatypes.
CAST will almost certainly come in very handy when you want to convert between types of collections, or if you want to convert a subquery directly into a collection.
To demonstrate these capabilities, I will use the following structures (see cast.sql for all of these statements and the actual exercises):
/* Filename on companion disk: cast.sql */ CREATE TYPE address_t AS OBJECT (street VARCHAR2(100), city VARCHAR2(100)); / CREATE TYPE address_book_t AS TABLE OF address_t; / CREATE TYPE address_array_t AS VARRAY(3) OF address_t; / CREATE TABLE addresses OF address_t; INSERT INTO addresses VALUES ( address_t('19th St', 'St. Louis')); INSERT INTO addresses VALUES ( address_t('Biglanes Blvd', 'Dallas')); INSERT INTO addresses VALUES ( address_t('Upanddown Lane', 'San Francisco'));
I now have a nested table, variable array, and database table all with the same structure. I can then use CAST -- along with the MULTISET operator in SQL -- to move between the different structures.
Suppose, for example, that I have a nested table declared in PL/SQL, and I want to move that to a VARRAY. Here are the steps I would take:
/* Filename on companion disk: cast.sql */ DECLARE addr_varray address_array_t; addr_ntable address_book_t := address_book_t ( address_t('Madison Ave', 'Chicago'), address_t('Devon Ave', 'Chicago')); BEGIN SELECT CAST (addr_ntable AS address_array_t) INTO addr_varray FROM dual;
I would love to be able to do this:
addr_varray := CAST (addr_ntable AS address_array_t); /* INVALID! */
Unfortunately, the CAST operator cannot be used directly (natively) in PL/SQL to operate on collections.
I can also transform the result set of a query into a nested table or VARRAY using CAST and MULTISET, as shown in the following:
/* Filename on companion disk: cast.sql */ DECLARE addr_varray address_array_t; BEGIN SELECT CAST (MULTISET ( SELECT street, city FROM addresses p ORDER BY city) AS address_array_t) INTO addr_varray FROM dual;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.