Page 149
This lesson examines the techniques that Oracle provides for manipulating strings, dates, and numbers. These techniques are useful in the following situations:
Oracle provides an extensive set of built-in functions and operators for the conversion and manipulation of strings, dates, and numbers. The true power of these functions and operators is realized by nesting these functions within each other. This lesson presents several examples of this technique.
Page 150
Oracle provides several useful built-in functions that can be used to manipulate strings. You'll see a number of examples in the following pages.
You can use the LENGTH function to find the length of a string column. LENGTH returns a number equal to the number of characters in the argument, as shown in Listing 7.1.
Listing 7.1. Using the LENGTH function.
SQL> select Last_Name, length(Last_Name)
3 order by Last_Name;
LAST_NAME LENGTH(LAST_NAME)
------------------------ -----------------
ANGELO 6
BATES 5
BILLINGS 8
CHANG 5
CHERNOW 7
CHU 3
DANIELS 7
EDWARDS 7
HITCHCOCK 9
JASON 5
NILAND 6
PARKER 6
POULSON 7
RESTON 6
RICHARDSON 10
TORRES 6
WEISS 5
YOUNG 5
18 rows selected.
The SUBSTR function extracts a substring from a string.
The SUBSTR function is used in the following way:
SUBSTR (string, starting character, number of characters)
The variables are defined as follows:
string is a character column or string expression.
starting character is the starting position of the substring.
number of characters is the number of characters to return.
Page 151
Listing 7.2 illustrates the use of the SUBSTR function to obtain the first four characters of the instructor's last name.
Listing 7.2. Using the SUBSTR function.
SQL> select Last_Name, substr(Last_Name,1,4)
2 from Instructor
3 order by Last_Name;
LAST_NAME SUBS
------------------------ ----
ANGELO ANGE
BATES BATE
BILLINGS BILL
CHANG CHAN
CHERNOW CHER
CHU CHU
DANIELS DANI
EDWARDS EDWA
HITCHCOCK HITC
JASON JASO
NILAND NILA
PARKER PARK
POULSON POUL
RESTON REST
RICHARDSON RICH
TORRES TORR
WEISS WEIS
YOUNG YOUN
18 rows selected.
In addition to using literal values in the SUBSTR function, you can use a function as an argument in the SUBSTR function. Listing 7.3 provides a good, although not very useful, example. Suppose you want to retrieve the last three characters of an instructor's last name. You would use the LENGTH function to find the last character position. To determine the correct starting character for the SUBSTR function, you subtract n _ 1 from LENGTH, where n is the number of characters that you want to retrieve—in Listing 7.3, 3 _ 1 = 2.
Listing 7.3. Using the LENGTH function within the SUBSTR function.
SQL> select substr(Last_Name,length(Last_Name)-2,3) 2 from Instructor 3 order by Last_Name; SUB ---
continues
Page 152
Listing 7.3. continued
ELO TES NGS ANG NOW CHU ELS RDS OCK SON AND KER SON TON SON RES ISS UNG 18 rows selected.
You learned how to use the LIKE operator on Day 5, "Introduction to Structured Query Language (SQL)." As a quick review, you can use the LIKE operator to search for patterns in string expressions. In fact, you can perform very specific searches with carefully constructed patterns.
Listing 7.4 illustrates how to use the LIKE operator if you want to retrieve all rows in which a course description contains the pattern theory or theories.
Listing 7.4. Using the LIKE operator.
SQL> column description format a40 word_wrapped
SQL> column title format a35
SQL> select Title, Description
2 from Course
3 where
4 Description like `%theory%' or
5 Description like `%theories%';
TITLE DESCRIPTION
------------------------------ ---------------------------------------
NUMBER THEORY Introduction to number theory;
characteristics of natural, rational,
and irrational numbers; survey of lucky
numbers
Page 153
INTRO TO PSYCHOLOGY A general introduction to the theory of
human psychology. Topics include
Freudian theory, theory of behavior,
psychological development of the child,
and other topics.
INTRO TO ECONOMICS A general introduction to the "dismal"
science of economics. Topics include
supply and demand, the theory of money,
theories of production, and other
relevant topics.
INTRO TO STRUCTURES A general introduction to the theory of
mechanical structures including levers,
gears, beams, and others.
MODERN PHILOSOPHY A workshop on recent philosophical
debates including bioethics,
communication theory, social net theory,
and philosophical relativism.
INTRO TO CIRCUIT THEORY A general introduction to the theory of
circuits, logical design, boolean
theory, and other topics.
INTRO TO DYNAMICS Introduction to the theory of dynamic
systems, both linear and nonlinear.
SEMINAR ON CHAOS Seminar will explore examples of chaos
theory as applied to economic,
biological, and physical systems. Class
schedule TBD.
EVOLUTIONARY GRAMMAR Seminar on current trends in English
grammar. Topics include apostrophe
theory and plurality.
WORKSHOP ON NEUROSES Intense workshop on the development and
perfection of various neuroses.
Exploration of neuroses-psychoses
transition theories.
10 rows selected.
Please note that the first two lines in Listing 7.4 are SQL*Plus commands. They were used to format the query output for this book.
A common data-manipulation task is transforming one pattern into another in a particular column. Suppose you wanted to change the course description in the Course table so that all descriptions that included the word seminar were replaced with the word discussion.
Page 154
Fortunately, Oracle provides a function, REPLACE, that is used to manipulate a column by replacing one string with another string.
The syntax for the REPLACE function is
REPLACE (string, existing_string, [replacement_string])
The variables are defined as follows:
string is a string expression. existing_string is a string that might occur in string. replacement_string is an optional string with which to replace existing_string.
Listing 7.5 demonstrates how REPLACE can be used to change a course title in the Course table. First, a query displays the current course titles. An UPDATE statement is used with the REPLACE function to change occurrences of SEMINAR to DISCUSSION. Another query shows the effect of the UPDATE statement.
Listing 7.5. Using the REPLACE function.
SQL> select Title
2 from Course
3 order by Title;
TITLE
---------------------------------
ABNORMAL PSYCHOLOGY
ADVANCED ARITHMETIC
ANCIENT GREEK PHILOSOPHERS
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