Chapter 16: Advanced Use of Functions and ppt

LPAD('o',ROUND(AVG(ReturnedDate-CheckOutDate)/2,0),'o')

AS Graph

from BOOKSHELF_CHECKOUT

group by Name

order by AVG(ReturnedDate-CheckOutDate);

Day

1 2 3 4 5 6 7

NAME DAYSOUT ....0....0....0....0....0....0....0

---------------- ------- -----------------------------------

DORAH TALBOT 13 ooooooo

EMILY TALBOT 14 ooooooo

JED HOPKINS 18 ooooooooo

GERHARDT KENTGEN 19 ooooooooo

PAT LAVAY 21 oooooooooo

FRED FULLER 24 oooooooooooo

ROLAND BRANDT 52 oooooooooooooooooooooooooo

7 rows selected.

This use of LPAD is similar to what was done in Chapter 13, where the cows and bulls

were shown in their family tree. Basically, a lowercase o is the column here, and it is padded

on the left with a number of additional lowercase o’s, to the maximum width determined by

ROUND(AVG(ReturnedDate-CheckOutDate)/2,0).

Notice that the scale of the column heading is in increments of two. A simple change to the

SQL will produce a classic graph rather than a bar chart. The literal column is changed from an

o to a lowercase x, and the padding on the left is done by spaces.

select Name,

AVG(ReturnedDate-CheckOutDate) DaysOut,

LPAD('x',ROUND(AVG(ReturnedDate-CheckOutDate)/2,0),' ')

AS Graph

from BOOKSHELF_CHECKOUT

group by Name

order by AVG(ReturnedDate-CheckOutDate);

Day

1 2 3 4 5 6 7

NAME DAYSOUT ....0....0....0....0....0....0....0

---------------- ------- -----------------------------------

DORAH TALBOT 13 x

EMILY TALBOT 14 x

JED HOPKINS 18 x

GERHARDT KENTGEN 19 x

PAT LAVAY 21 x

FRED FULLER 24 x

ROLAND BRANDT 52 x

7 rows selected.

Chapter 16: Advanced Use of Functions and Variables 299

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:299

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:21 PM

Color profile: Generic CMYK printer profile

Composite Default screen

300 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:300

Another way to graph the data is by its distribution rather than by person. First, a view is

created that puts each number of days out into its decile. Thus 13, 14, 18, and 19 become 10;

20 through 29 become 20; 30 through 39 become 30; and so on:

create or replace view DaysOutRange as

select ROUND((ReturnedDate-CheckOutDate),-1) Decile,

COUNT(*) Counter

from BOOKSHELF_CHECKOUT

group by ROUND((ReturnedDate-CheckOutDate),-1);

Next, a column heading is set up, similar to the previous heading although shorter and in

increments of one:

column Graph Heading 'Count| 1 1|....5....0....5'-

justify c

column Graph format a15

The next SQL determines the count of values that are represented in each decile.

select Decile, Counter,

LPAD('o',Counter,'o') AS Graph

from DAYSOUTRANGE;

Count

1 1

DECILE COUNTER ....5....0....5

---------- ---------- ---------------

10 8 oooooooo

20 5 ooooo

30 3 ooo

50 2 oo

60 1 o

If one of the dates were NULL, the group by output would include a NULL group, with a

count and display similar to those shown in this listing. If you want to customize how Oracle

handles books with NULL ReturnedDate values, you can use the NVL function to replace the

NULL value with one of your choosing (such as SysDate). For more complex data replacement

logic, you can use DECODE and CASE, as introduced later in this chapter and more fully

described in Chapter 17.

Using TRANSLATE

TRANSLATE converts characters in a string into different characters, based on a substitution plan

you give it, from if to then:

TRANSLATE(string,if,then)

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:22 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 16: Advanced Use of Functions and Variables 301

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:301

In the following SQL, the letters in a sentence are replaced. Any time an uppercase T is

detected, it is replaced by an uppercase T. In effect, nothing changes. Any time an uppercase

vowel is detected, however, it is replaced by a lowercase version of the same vowel. Any

letter not in the TAEIOU string is left alone. When a letter is found in TAEIOU, its position is

checked in the TAEIOU string, and the letter there is substituted. Thus, the letter E, at position

3 in TAEIOU, is replaced by e, in position 3 of Taeiou:

select TRANSLATE('NOW VOWELS ARE UNDER ATTACK','TAEIOU','Taeiou')

from DUAL;

TRANSLATE('NOWVOWELSAREUNDE

---------------------------

NoW VoWeLS aRe uNDeR aTTaCK

Eliminating Characters

Extending this logic, what happens if the if string is TAEIOU and the then string is only T ?

Checking for the letter E (as in the word VOWELS) finds it in position 3 of TAEIOU. There is

no position 3 in the then string (which is just the letter T ), so the value in position 3 is nothing.

So E is replaced by nothing. This same process is applied to all of the vowels. They appear in

the if string, but not in the then string. As a result, they disappear, as shown here:

select TRANSLATE('NOW VOWELS ARE UNDER ATTACK','TAEIOU','T')

from DUAL;

TRANSLATE('NOWVOWE

------------------

NW VWLS R NDR TTCK

This feature of TRANSLATE, the ability to eliminate characters from a string, can prove very

useful in cleaning up data. Recall the magazine titles in Chapter 7:

select Title from MAGAZINE;

TITLE

-------------------------------------

THE BARBERS WHO SHAVE THEMSELVES.

"HUNTING THOREAU IN NEW HAMPSHIRE"

THE ETHNIC NEIGHBORHOOD

RELATIONAL DESIGN AND ENTHALPY

"INTERCONTINENTAL RELATIONS."

The method used in Chapter 7 to clean out the periods and double quotes was a nested

combination of LTRIM and RTRIM:

select LTRIM( RTRIM(Title,'."') ,'"') from MAGAZINE;

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:22 PM

Color profile: Generic CMYK printer profile

Composite Default screen

The same goal can be accomplished with a single use of TRANSLATE:

select TRANSLATE(Title,'T".','T') AS TITLE

from MAGAZINE;

TITLE

-------------------------------------

THE BARBERS WHO SHAVE THEMSELVES

HUNTING THOREAU IN NEW HAMPSHIRE

THE ETHNIC NEIGHBORHOOD

RELATIONAL DESIGN AND ENTHALPY

INTERCONTINENTAL RELATIONS

In the listing, you have to include one character in the then string—in this case, the letter T

translates to the letter T. All other characters in the if set are eliminated.

Complex Cut and Paste

The NAME table contains a list of names as you might receive them from a mailing list company

or another application. First name, last name, and initials are all in one column:

column Name format a25

select Name from NAME;

NAME

-------------------------

HORATIO NELSON

VALDO

MARIE DE MEDICIS

FLAVIUS JOSEPHUS

EDYTHE P. M. GAMMIERE

Suppose you want to cut and paste these names and put them into a table with FirstName

and LastName columns. How would you go about it? The technique you learned in Chapter 7

involved using INSTR to locate a space, and using the number it returns in a SUBSTR to clip

out the portion up to that space. Here’s an attempt to do just that for the first name:

select SUBSTR(Name,1,INSTR(Name,' '))

from NAME;

SUBSTR(NAME,1,INSTR(NAME,

-------------------------

HORATIO

MARIE

FLAVIUS

EDYTHE

302 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:302

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:22 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 16: Advanced Use of Functions and Variables 303

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:303

VALDO has vanished! The problem is that these names are not as consistent as the authors’

names were in Chapter 7. One of these names (probably a magician) is only one name long, so

there is no space for the INSTR to find. When INSTR has an unsuccessful search, it returns a 0.

The SUBSTR of the name VALDO, starting at position 1 and going for 0 positions, is nothing, so

he disappears. This is solved with DECODE. In place of this:

INSTR(Name,' ')

you put the entire expression, like this:

DECODE(INSTR(Name,' '),0,99,INSTR(Name,' '))

DECODE’s format is this:

DECODE(value,if1,then1[,if2,then2,if3,then3]...,else)

In the preceding example, DECODE tests the value of INSTR(Name, ‘ ‘). If it is equal to 0,

DECODE substitutes 99; otherwise, it returns the default value, which is also INSTR(Name, ‘ ‘).

The choice of 99 as a substitute is arbitrary. It will create an effective SUBSTR function for

VALDO that looks like this:

SUBSTR('VALDO',1,99)

This works because SUBSTR will clip out text from the starting number, 1, to the ending

number or the end of the string, whichever comes first. With the DECODE function in place,

the first names are retrieved correctly:

select SUBSTR(Name,1,

DECODE(INSTR(Name,' '),0,99,INSTR(Name,' ')))

from NAME;

SUBSTR(NAME,1,DECODE(INST

-------------------------

HORATIO

VALDO

MARIE

FLAVIUS

EDYTHE

How about the last names? You could use INSTR again to search for a space, and use the

location of the space in the string, plus one (+1), as the starting point for SUBSTR. No ending

point is required for SUBSTR, because you want it to go to the end of the name. This is what

happens:

select SUBSTR(Name,INSTR(Name,' ')+1)

from NAME;

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:23 PM

Color profile: Generic CMYK printer profile

Composite Default screen

304 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:304

SUBSTR(NAME,INSTR(NAME,''

-------------------------

NELSON

VALDO

DE MEDICIS

JOSEPHUS

P. M. GAMMIERE

This didn’t quite work. One solution is to use three INSTR functions, looking successively

for the first, second, or third occurrence of a space in the name. Each of these INSTRs will return

either the location where it found a space or a 0 if it didn’t find any. In a name with only one

space, the second and third INSTRs will both return 0. The GREATEST function, therefore, will

pick the number returned by the INSTR that found the space furthest into the name:

select SUBSTR(Name,

GREATEST(INSTR(Name,' '),INSTR(Name,' ',1,2),

INSTR(Name,' ',1,3)) +1)

from NAME;

SUBSTR(NAME,GREATEST(INST

-------------------------

NELSON

VALDO

MEDICIS

JOSEPHUS

GAMMIERE

Except for the fact that you also got VALDO again, this worked. (GREATEST also could have

been used similarly in place of DECODE in the previous example.) There is a second and simpler

method:

select SUBSTR(Name,INSTR(Name,' ',-1)+1)

from NAME;

SUBSTR(NAME,INSTR(NAME,''

-------------------------

NELSON

VALDO

MEDICIS

JOSEPHUS

GAMMIERE

The -1 in the INSTR tells it to start its search in the final position and go backward, or right

to left, in the Name column. When it finds the space, INSTR returns its position, counting from

the left as usual. The -1 simply makes INSTR start searching from the end rather than from the

beginning. A -2 would make it start searching from the second position from the end, and so on.

The +1 in the SUBSTR command has the same purpose as in the previous example: Once the

space is found, SUBSTR has to move one position to the right to begin clipping out the Name. If

no space is found, the INSTR returns 0, and SUBSTR therefore starts with position 1. That’s why

VALDO made the list.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:23 PM

Color profile: Generic CMYK printer profile

Composite Default screen

How do you get rid of VALDO? Add an ending position to the SUBSTR instead of its default

(which goes automatically all the way to the end). The ending position is found by using this:

DECODE(INSTR(Name,' '),0,0,LENGTH(Name))

which says, “Find the position of a space in the Name. If the position is 0, return 0; otherwise,

return the LENGTH of the Name.” For VALDO, the DECODE produces 0 as the ending position

for SUBSTR, so nothing is displayed. For any other name, because there is a space somewhere,

the LENGTH of the Name becomes the ending position for the SUBSTR, so the whole last name

is displayed.

This is similar to the DECODE used to extract the first name, except that the value 99 used

there has been replaced by LENGTH(Name), which will always work, whereas 99 would fail

for a name longer than 99 characters. This won’t matter here, but in other uses of DECODE and

SUBSTR, it could be important:

select SUBSTR(Name,

INSTR(Name,' ',-1)+1,

DECODE(INSTR(Name,' '), 0, 0, LENGTH(Name)))

from NAME;

SUBSTR(NAME,INSTR(NAME,''

-------------------------

NELSON

MEDICIS

JOSEPHUS

GAMMIERE

This DECODE also could have been replaced by a GREATEST:

select SUBSTR(Name,

INSTR(Name,' ',-1)+1,

GREATEST(INSTR(Name,' '),0))

from NAME;

A third method to accomplish the same end uses RTRIM. Remember that RTRIM eliminates

everything specified in its set from the right side of a string until it encounters any character not

in its set. The RTRIM here effectively erases all the letters on the right until it hits the first space

(just before the last name) or reaches the beginning of the string:

select

SUBSTR(Name,LENGTH(RTRIM(NAME,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))

+1)

from NAME;

SUBSTR(NAME,LENGTH(RTRIM(

-------------------------

NELSON

Chapter 16: Advanced Use of Functions and Variables 305

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:305

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:24 PM

Color profile: Generic CMYK printer profile

Composite Default screen

MEDICIS

JOSEPHUS

GAMMIERE

LENGTH then measures the resulting string (with the last name erased). This tells you the

position of the space before the last name. Add 1 to this number, do a SUBSTR starting there,

and you’ll get just the last name. Let’s create a table with FirstName and LastName columns

(you’ll see complete details on creating tables in Chapter 18):

create table TWONAME(

FirstName VARCHAR2(25),

LastName VARCHAR2(25)

);

Table created.

Now, use an insert with a subquery to load the table data with the first and last names from

the NAME table:

insert into TWONAME (FirstName, LastName)

select

SUBSTR(Name,1,DECODE(INSTR(Name,' '),0,99,INSTR(Name,' '))),

SUBSTR(Name,LENGTH(RTRIM(NAME,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))+1)

from NAME;

Check the contents of the TWONAME table:

select * from TWONAME;

FIRSTNAME LASTNAME

------------------------- -------------------------

HORATIO NELSON

VALDO

MARIE MEDICIS

FLAVIUS JOSEPHUS

EDYTHE GAMMIERE

You can use similar techniques to extract the middle initial or initials, and apply these methods

elsewhere as well, such as to addresses, product descriptions, company names, and so on.

When moving data from an old system to a new one, reformatting is frequently necessary

and often difficult. The facilities exist in SQL, but they require some knowledge of how the

functions work and some thoughtfulness to avoid the kinds of difficulties shown in the examples

so far in this chapter.

306 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:306

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:24 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 16: Advanced Use of Functions and Variables 307

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:307

Counting String Occurrences

Within Larger Strings

You can use a combination of the LENGTH and REPLACE functions to determine how many

times a string (such as ABC) occurs within a larger string (such as ABCDEFABC). The REPLACE

function replaces a character or characters in a string with zero or more characters. Thus,

REPLACE('ADAH', 'A', 'BLAH')

will evaluate the string ADAH. Everywhere an A is found, it will be replaced with the string

BLAH. Thus, the function shown in this example will return the string BLAHDBLAHH.

You can use the REPLACE function to eliminate characters from strings. For example, you

can replace the character string you’re searching for with NULL values. Thus,

REPLACE('GEORGE', 'GE', NULL)

returns a value of OR. The two separate occurrences of GE in GEORGE were each set to NULL.

You can use the REPLACE function to determine how many times a string (like GE) is found

in a larger string (like GEORGE). First, replace the string with NULL values:

select REPLACE('GEORGE', 'GE', NULL)

from DUAL;

The result of that query will be OR:

RE

--

OR

More importantly, the LENGTH of the result of that query will provide information about

how many times the string was found. The following query will tell you how long the resulting

string is:

select LENGTH(REPLACE('GEORGE', 'GE', NULL))

from DUAL;

LENGTH(REPLACE('GEORGE','GE',NULL))

-----------------------------------

2

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:24 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Now you can tell how many times the string was found. If you subtract the length of the

shortened string from the original string and divide that difference by the length of the search

string, the result will be the number of times the search string was found:

select (LENGTH('GEORGE')

- LENGTH(REPLACE('GEORGE', 'GE', NULL)) )

/ LENGTH('GE') AS Counter

from DUAL;

COUNTER

----------

2

This example uses strings instead of character columns in order to be simpler to understand;

in real applications, you would replace the original string (GEORGE) with your column name.

The length of the string GEORGE is six characters. The length of GEORGE after GE is replaced

with NULL is two characters. Therefore, the difference in the lengths of the original and

shortened strings is four characters. Dividing four characters by the length of the search string

(two characters) tells you that the string was found twice.

The only problem with this method occurs when the search string is zero characters in length

(since you cannot divide by zero). Searching for a zero-length string may indicate a problem with

the application logic that initiated the search.

Additional Facts About Variables

The command accept forces SQLPLUS to define the variable as equal to the entered value,

and it can do this with a text message, with control over the datatype entered, and even with

the response blanked out from viewing (such as for passwords; see the entry for accept in the

Alphabetical Reference).

You can pass arguments to a start file when it is started by embedding numbered variables

in the select statements (rather than variables with names).

To select all of the checkout records between one date and another, the select statement

might look like this:

select * from BOOKSHELF_CHECKOUT

where CheckOutDate BETWEEN '&1' AND '&2';

That query can be saved as a file (such as checkout.sql) and started from within SQLPLUS:

start checkout.sql 01-JAN-02 31-JAN-02

The start file checkout.sql would begin, with 01-JAN-02 substituted for &1, and 31-JAN-02

substituted for &2. As with other variables, character and DATE datatypes must be enclosed

in single quotation marks in the SQL statement. One limitation of this is that each argument

following the word start must be a single word without spaces.

Variable substitutions are not restricted to the SQL statement. The start file also may use

variables for such things as SQLPLUS commands.

308 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 16

Blind Folio 16:308

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:12:25 PM

Color profile: Generic CMYK printer profile

Composite Default screen