Page 159
LPAD(MY_COL,LENGTH(MY_COL)+16,'YOUSAY') ---------------------------------------------------------------------- You say You say Hello, world You say You say Goodbye, cruel world
You can also left-pad a string with the contents of another column. As you can see in the second query in Listing 7.11, MY_COL2 is left padded onto MY_COL. The number of times that this step is performed depends on the length of values in both columns. If MY_COL is equal to California and MY_COL2 is equal to Los Angeles, LPAD returns a string in which California (10 characters) is left padded with Los Angeles (11 characters) so that Los Angeles fills 40 characters.
Listing 7.11. Using LPAD to concatenate two columns.
SQL> select * from test_trim; MY_COL MY_COL2 ------------------------------ ------------------------------ California Los Angeles Michigan Jackson Washington Seattle Oregon Portland SQL> select lpad(my_col,50,my_col2) from test_trim; LPAD(MY_COL,50,MY_COL2) -------------------------------------------------- Los AngelesLos AngelesLos AngelesLos AngCalifornia JacksonJacksonJacksonJacksonJacksonJacksonMichigan SeattleSeattleSeattleSeattleSeattleSeattWashington PortlandPortlandPortlandPortlandPortlandPortOregon
By combining these built-in functions, you can assemble elaborate expressions. Listing 7.12 uses the lengths of MY_COL and MY_COL2 as arguments to guarantee that left-padding occurs only once. Let's dissect the SELECT statement. LPAD's first argument is MY_COL2. For the second argument, you add the length of MY_COL2 to the length of MY_COL2 and add an additional 2 for the `, ` that will be placed between MY_COL and MY_COL2. Finally, for LPAD's third argument, you concatenate MY_COL2 with , .
Listing 7.12. Using LENGTH as an argument in LPAD.
SQL> select lpad (my_col, Âlength(my_col)+length(my_col2)+2, my_col2 || `, `) 2 from test_trim; LPAD(MY_COL,LENGTH(MY_COL)+LENGTH(MY_COL2)+2,MY_COL2||', `) ---------------------------------------------------------------------- Los Angeles, California Jackson, Michigan Seattle, Washington Portland, Oregon
Page 160
RPAD works just like LPAD.
Use the following syntax:
RPAD (string, n, pad_string)
The variables are defined as follows:
string is the literal string or string column to be right-padded. n is the number of times to right-pad pad_string. pad_string is the string to right-pad onto string.
Oracle provides three functions that enable you to change the case of a string's characters:
All three functions have a single argument: the string expression to be manipulated. Listing 7.13 demonstrates the use of the UPPER and LOWER functions.
Listing 7.13. Using the UPPER and LOWER functions.
SQL> select lower(Title), upper(Title) 2 from Course 3 order by Title; LOWER(TITLE) UPPER(TITLE) ---------------------------------- ---------------------------------- abnormal psychology ABNORMAL PSYCHOLOGY advanced arithmetic ADVANCED ARITHMETIC ancient greek philosophers ANCIENT GREEK PHILOSOPHERS early american history EARLY AMERICAN HISTORY evolutionary grammar EVOLUTIONARY GRAMMAR general calculus GENERAL CALCULUS intro to anthropology INTRO TO ANTHROPOLOGY intro to biology INTRO TO BIOLOGY intro to circuit theory INTRO TO CIRCUIT THEORY intro to dynamics INTRO TO DYNAMICS intro to economics INTRO TO ECONOMICS intro to english lit INTRO TO ENGLISH LIT intro to philosophy INTRO TO PHILOSOPHY intro to psychology INTRO TO PSYCHOLOGY intro to structures INTRO TO STRUCTURES invertebrate anatomy INVERTEBRATE ANATOMY mammalian anatomy MAMMALIAN ANATOMY modern english lit MODERN ENGLISH LIT modern european history MODERN EUROPEAN HISTORY
Page 161
modern philosophy MODERN PHILOSOPHY monetary policy MONETARY POLICY number theory NUMBER THEORY phys anthro field techniques PHYS ANTHRO FIELD TECHNIQUES pre-calculus PRE-CALCULUS psych in film PSYCH IN FILM seminar on chaos SEMINAR ON CHAOS seminar on nacimera SEMINAR ON NACIMERA seminar on theme analysis SEMINAR ON THEME ANALYSIS workshop on jefferson WORKSHOP ON JEFFERSON workshop on marx WORKSHOP ON MARX workshop on neuroses WORKSHOP ON NEUROSES workshop on normality WORKSHOP ON NORMALITY 32 rows selected.
Listing 7.14 illustrates how the INITCAP function will convert all characters to lowercase and capitalize the first letter of each word.
Listing 7.14. Using the INITCAP function.
SQL> select initcap(Title)
2 from Course
3 order by Title;
INITCAP(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
continues
Page 162
Listing 7.14. continued
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.
Many database applications reference columns that contain encoded information. Sometimes a database designer creates a table to store a code and its description, especially if the designer expects the codes to change. In other situations, the column containing the code stands alone without any additional description available in the database.
Day 3, "Logical Database Design," discussed the need for a table that would contain the details of a particular schedule—which days the class would meet and for how long. As a result, you created the Schedule_Type_Details table. One of the columns in this table, Day, stores the day of the week as an integer with Sunday represented as 1, Monday represented as 2, and so on.
Most users will find it inconvenient to decipher the contents of this table. Fortunately, the DECODE function can translate cryptic codes into something that users will have no difficulty in interpreting.
Its syntax is
DECODE (expression, value1, returned_value1, ... valueN, returned_valueN, [default_returned_value])
The variables are defined as follows:
expression is a valid Oracle expression.
valueN is a possible value to which expression might be equal.
returned_valueN is the value returned by DECODE if expression is equal to valueN.
default_returned_value is an optional value returned by DECODE if expression is not equal to any of the values, value1 through valueN.
Listing 7.15 shows how the DECODE function can translate the numeric Day into the day
of the week.