Manipulating Letters and Numbers

Manipulating Letters and Numbers

Strings

This section takes us into the area of strings, which are simply sequences of things, like the letters in this sentence or your name . In the course of your work you'll have to play with strings, and fortunately SQL comes with several good editing tools to help you:

• CONCAT . This command, or , is used to concatenate several strings. For example, either of the following two commands:

  1. SELECT L_NAMEF_NAME from STUDENTS ^[1]

     ^[1] Use the broken vertical bar to concatenate strings.

  2. SELECT CONCAT (L_NAME,F_NAME) from STUDENTS; yields this report:

     CONCAT(L_NAME,F_NAME)

     RandolphJared

     WilliamsJacob

     CohenGeorge

     ChampagneAlice

     KlienMarisha

     SanpleticMartha

     LevinDonald

     CorsuFrank

• LOWER . For example, the command LOWER ('GUERRILLA ORACLE') produces "guerrilla oracle".

• UPPER . For example, the command SELECT UPPER(L_NAME) from STUDENTS produces all student names in uppercase letters.

• INITCAP . For example, the command SELECT INITCAP(LOWER(L_NAME)) ^[2] from STUDENTS; gives the following:

  ^[2] Notice that you have to use both INITCAP and LOWER to get the first letter of the last name as an uppercase letter.

  Randolph

  Williams

  Cohen

  Champagne

  Klien

  Sanpletic

  Levin

  Corsu

• LENGTH . For example, the command SELECT LENGTH(L_NAME) from STUDENTS gives the length of each last name.

• SUBSTR . This command is used to get a piece of a string. For example, the command SELECT SUBSTR(L_NAME, 3,5) from STUDENTS; yields this result:

  SUBST

  ndolp ^[3]

  lliam

  hen

  ampag

  ien

  nplet

  vin

  rsu

  ^[3] The SUBSTR command above took part of the last name: It started at position 3 and went for the next five characters .

• SOUNDEX . This fun command looks for strings that sound like the string you're using to compare. (Note that spelling does count!). Here's an example:

   SELECT L_NAME from STUDENTS        WHERE SOUNDEX(L_NAME) = SOUNDEX('willyams'); 

  This command returns the name "Williams". But be aware that SOUNDEX assumes American English! Results are not predictable with other languages, especially with foreign names.

• INSTR . This command is used to look for the location of a particular character in a string. For example, the command SELECT L_NAME INSTR(L_NAME, 'A') from STUDENTS gives these results:

  L_Name INSTR(L_Name,'A')
  Randolph	[4]
  Williams
  Cohen
  Champagne
  Klien
  Sanpletic
  Levin
  Corsu

  ^[4] Notice zero hits for A. Now try it with a small a.

Here's the same SELECT statement, but using a small a :

 SELECT L_NAME INSTR(L_NAME, 'a') from STUDENTS; 

The results look like this:

L_Name INSTR(L_Name,'a')
Randolph	2 [5]
Williams	6
Cohen
Champagne	3
Klien
Sanpletic	2
Levin
Corsu

^[5] Now you can see the position of the first occurrence of the letter a in each last name in the table.

The INSTR command is extremely useful when you're searching the database for particular patterns, names, invalid strings, and so forth. You'll probably be using it a lot.

For our final discussion of strings, we're going to take a look at four more tools that you can use to manipulate a string or set: LPAD , RPAD , LTRIM , and RTRIM . (You already know that SUBSTR lets you find a piece of a string.)

The PAD twins ” R and L ”let you add characters to the (guess where?) right and left ends of a string. By default the characters are spaces, but you can use anything. Common uses of RPAD and LPAD are to add periods or dashes to help the user read across a line. Here's an example:

 SELECT  RPAD  (L_NAME,40,'.'), F_NAME from STUDENTS; 

This command produces the following report:

RPAD(L_Name,40,'.') F_Name
Randolph............................ ......	Jared [6]
Williams.............................. .....	Jacob
Cohen................................. ... ...	George
Champagne...............................	Alice
Klien................................. ... ....	Marisha
Sanpletic............................ ... ...	Martha
Levin................................. ... ...	Donald
Corsu................................ ... ....	Frank

^[6] We used RPAD to add periods to the right of the last name.

Now let's look at the TRIM twins: LTRIM and RTRIM . These commands are used to remove characters at the ends of strings, such as quotation marks. Here are a few examples:

• SELECT RTRIM (TEST_NAME, '"') from TEST_ID;

  This command gets rid of the quotation mark at the end of a string. (Note that the double quote is enclosed within single quotes.)

• SELECT RTRIM (TEST_NAME, 'Ph.D') from TEST_ID;

  This command drops "Ph.D." from the end of a string.

• SELECT LTRIM ( RTRIM (TEST_NAME, 'Ph.D"') '"') from TEST_ID;

  This command gets rid of quotation marks at the beginning and end of a string, as well as "Ph.D." at the end. Notice that we're modifying both ends here: LTRIM gets rid of the quotation mark on the left, and RTRIM takes care of "Ph.D." and the quotation mark on the right. Just be careful with your parentheses.

More Functions for Working with Strings

TRANSLATE

Suppose you wanted to modify last names by changing certain characters. You would use the Oracle TRANSLATE command as in this example:

 SQL> 1* select translate(L_NAME, 'R', 'x') from students 

Here are the results:

TRANSLATE(L_NAME,'R'

xandolph ^[1]

Williams

Cohen

Champagne

Klien

Sanpletic

Levin

Corsu

^[1] Notice that "Randolph" is now "xandolph". No other names were changed because there weren't any more capital Rs.

You can use this technique to eliminate characters by putting blanks in the TRANSLATE command:

 SQL> SELECT TRANSLATE(L_NAME, 'R', ' ') from STUDENTS  

(2) Notice that this command says to replace any capital R with a blank space. That's how it works: Specify the character(s) you want to replace with what you want them replaced with.

The results look like this:

TRANSLATE(L_NAME,'R'

andolph ^[3]

Williams

Cohen

Champagne

Klien

Sanpletic

Levin

Corsu

^[3] Notice that "Randolph" is now " andolph".

DECODE

DECODE is an interesting function. I use it to reverse financial transactions where a duplicate journal entry has to be made but the sign has to be reversed . Here's the general syntax:

DECODE (value, if it is this1(a), then change it to this1(b), if it is this2(a), then change it to this2(b), . . . else change it to this)

Here's an example:

DECODE can be a very powerful tool when used with other functions. Such programming gets rather complex, and I refer you to more detailed documentation on DECODE. What I have shown you here will more than suffice for most of your normal work, but for those who are curious and have taken a liking to such functions, go and look deeper.

Number Functions

In this short section I'm going to introduce you to some of the more common mathematical functions that are used in normal business environments. For the serious mathematicians out there, be aware that there is much, much more horsepower in Oracle math functions than we're going to cover here, and more in-depth references are available when you need them.

We're going to take a look at AVG , COUNT , MAX , MIN , SUM , GREATEST , and LEAST . Before we start, keep in mind these two things:

1. We use the normal math symbols (+, “, *, /) when specifying any calculations. Here's an example:

    Select FEES, FINES, PARKING, FEES + FINES + PARKING TOTALS from registration_record; 

2. NULL is not zero. It is a special Oracle construct that means that the field is empty. This is important.

NULL

Let's digress a bit and talk about NULL . In Oracle, as I have mentioned, NULL means "empty"; it does not mean zeros or spaces. It is an intrinsic feature, and when you run the DESC command on a table, one of the headings will be "NULL?". For example, this command:

 SQL> desc temp_person_table; 

yields the following results:

Name	Null?	Type
Person_ID		NUMBER(12)
Hours		NUMBER(9,2)
Run_Date		DATE

Here none of the columns are set to NULL , which is standard. However, there are some important considerations with NULL values.

First, any mathematical computation that uses a NULL value ends up staying null. For example, the following code inserts data for a particular person into COURSE_TABLE with a NULL value for HOURS , and then increases the hours for that person:

 SQL> RUN   1 INSERT into COURSE_TABLE values (   2* 987654321, 16, '12-APR-02', 'Satellite', 65.25,  null  )  1 row created. SQL> RUN   1* UPDATE COURSE_TABLE set hours = (hours +12) WHERE PERSON_ID = 987654321 1 row updated. 

(1) Notice NULL value in the 'insert' command.

Now take a look at the HOURS value for person 987654321. You would expect 12 hours, but it is still NULL . Run a simple SQL statement to select everything from the COURSE_TABLE :

 SQL> select * from course_table; 

The results look like this:

PERSON_ID	COURSE_ID	DATE_TAKE	LOCATION	GRADE	HOURS
987654321	16	12-APR-02	Satellite	65.25
222222222	2	15-JAN-02	Home Office, Lab 12	81.5	18

Now that you know how NULL values work, think about other implications. Other math activities, such as AVG , SUM , MIN , MAX , and COUNT will skip any NULL columns, possibly making the results meaningless. You could do a count on all the rows in a table, then do a count on just one column, and get different results. The reason is that COUNT will count all rows but will skip NULL columns!

You can test for NULL values, but you must use the correct syntax. Instead of the equal sign and so forth, you must use the following general syntax: "If Person_ID IS NULL . . . (do something)", or "If Person_ID IS NOT NULL . . . (do something)".

Now let's take a look at the test scores to date for all students:

SCORE [2]
98	88	100
90	32	100
50	75	100
87	22	100
92	98	80
93	90	100
79	91	100
72	80	100
		100

^[2] So far, we have 25 students who have taken Guerrilla Waiver exams, with scores ranging from 22 to 100.

Suppose that the director of training comes to you and wants a quick synopsis of the scores ”no details, just a summary. Here's where you can combine math functions and deliver a neat, quick, easily understood overview. Just use the following SELECT command:

 SQL> SELECT AVG(SCORE), COUNT(SCORE), MAX(SCORE), MIN(SCORE), SUM(SCORE)   2 from TEST_HISTORY; 

And here are the results:

AVG(SCORE)	COUNT(SCORE)	MAX(SCORE)	MIN(SCORE)	SUM(SCORE)
84.68	25	100	22	2117

Note the use of the various math commands, and when the director comes to you and says she would like a breakdown so that she can see which exam is getting the lowest score, you refine the SELECT command like this:

 SELECT TEST_ID, AVG(SCORE) from TEST_HISTORY  GROUP BY  TEST_ID;  

(3) Notice the GROUP BY statement. We must use this because we're asking Oracle to give information on a group of rows, not just one row. Any time we ask for such a function as SUM, COUNT, or AVG, we must include the GROUP BY clause.

The results look like this:

TEST_ID	AVG(SCORE)
1	84.6
2	60.8
3	94.6
4	90.8
5	92.6

Now that the director can see the average scores, she asks you to show details for Test 2. Just keep refining your SELECT statement, as here:

 SQL> SELECT STUDENT_ID, SCORE from TEST_HISTORY          WHERE TEST_ID = 2 

Now we get these results:

STUDENT_ID	SCORE
111111111	50
222222222	32
333333333	22
444444444	100
555555555	100

Since you've done such a good job getting the information so quickly, the director now wants to see how student 111111111 has done on all tests. By now I'm sure you can figure out the SELECT command for that!

But what I want you to realize is that you have really created a group of queries that should become standard reports that the director can run when-ever she wants. So go ahead and create the scripts, using standard coding, as well as prompting the user for input, such as which test or which student. In going through these rather simple exercises, you will have discovered what is valuable to your users.