Page 155
SEMINAR ON CHAOS SEMINAR ON NACIMERA SEMINAR ON THEME ANALYSIS WORKSHOP ON JEFFERSON WORKSHOP ON MARX WORKSHOP ON NEUROSES WORKSHOP ON NORMALITY 32 rows selected. SQL> update Course 2 set Title = replace(Title,'SEMINAR','DISCUSSION'); 32 rows updated. SQL> select Title 2 from Course 3 order by Title; TITLE ---------------------------------- ABNORMAL PSYCHOLOGY ADVANCED ARITHMETIC ANCIENT GREEK PHILOSOPHERS DISCUSSION ON CHAOS DISCUSSION ON NACIMERA DISCUSSION ON THEME ANALYSIS EARLY AMERICAN HISTORY EVOLUTIONARY GRAMMAR GENERAL CALCULUS INTRO TO ANTHROPOLOGY INTRO TO BIOLOGY INTRO TO CIRCUIT THEORY INTRO TO DYNAMICS INTRO TO ECONOMICS INTRO TO ENGLISH LIT INTRO TO PHILOSOPHY INTRO TO PSYCHOLOGY INTRO TO STRUCTURES INVERTEBRATE ANATOMY MAMMALIAN ANATOMY MODERN ENGLISH LIT MODERN EUROPEAN HISTORY MODERN PHILOSOPHY MONETARY POLICY NUMBER THEORY PHYS ANTHRO FIELD TECHNIQUES PRE-CALCULUS PSYCH IN FILM WORKSHOP ON JEFFERSON WORKSHOP ON MARX WORKSHOP ON NEUROSES WORKSHOP ON NORMALITY 32 rows selected.
Page 156
| NOTE |
|
If a character column contains leading or trailing spaces, a query based on a specified value for the column might return misleading results. To illustrate this point, take a look at Listing 7.6. First, an UPDATE statement is used to add a trailing space to First_Name. A query of the Instructor table for instructors whose first name is BORIS doesn't return any records, but a second query looking for a first name equal to `BORIS ` returns the row that was modified.
Listing 7.6. Dealing with trailing spaces.
SQL> update Instructor
2 set First_Name = First_Name || ` `;
18 rows updated.
SQL> select Instructor_ID, Last_Name, First_Name
2 from Instructor
3 where
4 First_Name = `BORIS';
no rows selected
SQL> select Instructor_ID, Last_Name, First_Name
2 from Instructor
3 where
4 First_Name = `BORIS `;
INSTRUCTOR_ID LAST_NAME FIRST_NAME
-------------------- ------------------------ ------------------------
M101 HITCHCOCK BORIS
Of course, if you hadn't been aware of the trailing space, you would have been surprised by the fact that no rows were returned by the query.
| TIP |
|
Page 157
Oracle provides two functions for trimming spaces: LTRIM and RTRIM. LTRIM removes leading spaces in a string, and RTRIM trims a string's trailing spaces.
To trim leading and trailing spaces from a string, simply embed the RTRIM function inside the LTRIM function. If no leading or trailing spaces occur, LTRIM and RTRIM won't modify the existing string. Refer to Listing 7.7 to see how the RTRIM function is used.
Listing 7.7. Using the RTRIM function to trim trailing spaces.
SQL> update Instructor 2 set First_Name = rtrim(First_Name) 3 where 4 First_Name like `% `; 18 rows updated.
Notice how the WHERE clause was used in Listing 7.7 to update only those rows in which First_Name contained a trailing space. RTRIM will trim trailing spaces without this WHERE clause. However, if you have a large table the performance of the UPDATE statement will be better if you use the WHERE clause to reduce the number of rows that RTRIM is applied against.
As you can see, LTRIM and RTRIM require a single argument: the string to be trimmed. You can also specify a second optional argument for both functions: an alternative set of characters to be trimmed from the string argument. As an example, please look at Listing 7.8. Suppose you have a table named Test_Trim with a single VARCHAR2 column named MY_COL. You know that some rows have leading characters that you want to trim off: x, y, and z. The LTRIM function is used in an UPDATE statement to remove the offending characters.
Listing 7.8. Using the LTRIM function to remove leading characters.
SQL> select my_col 2 from test_trim; MY_COL -------------------------------------------------------------- yzzxHello, world zyxGoodbye, cruel world SQL> update test_trim 2 set my_col = ltrim(my_col,'xyz'); 2 rows updated. SQL> select my_col from test_trim; MY_COL ------------------------------------------------------------ Hello, world Goodbye, cruel world
Page 158
You can eliminate other trailing characters by employing the same technique using RTRIM.
At some point, you may find yourself in a situation in which you need to pad a string with leading or trailing characters. Oracle provides two functions for this purpose: LPAD and RPAD.
To left-pad a string, use the LPAD function.
The syntax is
LPAD (string, n, pad_string)
The variables are defined as follows:
string is the literal string or string column to be left padded. n is the total length of the string returned by LPAD. pad_string is the string to left-pad onto string.
Let's see how this works. Listing 7.9 shows how LPAD can pad a column with leading spaces.
Listing 7.9. Using LPAD to add leading spaces to a column.
SQL> select lpad(my_col,20) from test_trim;
LPAD(MY_COL,20)
--------------------
Hello, world
Goodbye, cruel world
When pad_string is not supplied as an argument, LPAD uses a space to left-pad the string. You can specify a literal string that LPAD will use to left-pad the string. However, the number of characters that are padded on the string depends on the value of n. Listing 7.10 demonstrates how you can add a fixed value to the LENGTH function as an argument to LPAD. By increasing the number added to LENGTH, you can left-pad the string with pad_string more than once, as seen in the second query.
Listing 7.10. Using the LENGTH function as an argument to the LPAD function.
SQL> select lpad(my_col,length(my_col)+8,'You say `) from test_trim; LPAD(MY_COL,LENGTH(MY_COL)+8,'YOUSAY') ---------------------------------------------------------------------- You say Hello, world You say Goodbye, cruel world SQL> select lpad(my_col,length(my_col)+16,'You say `) from test_trim;