This chapter examines the techniques that Oracle provides for manipulating strings. These techniques are useful in the following situations:
Oracle provides an extensive set of built-in functions and operators for string manipulation. The real power of these functions and operators is realized by nesting these functions within each other. Several examples of the nesting technique appear in this chapter.
The LENGTH function finds the length of a string column. LENGTH returns a number equal to the number of characters in the argument, as the following code shows:
SQL> select Last_Name, length(Last_Name) 2 from Customer 3 order by Last_Name; LAST_NAME LENGTH(LAST_NAME) ------------------------------ ----------------- Chen 4 Fleming 7 Fray 4 Hernandez 9 Horace 6 Jensen 6 Johnson 7 Kramden 7 Martinez 8 Mcclintock 10 Moran 5 Pareski 7 Richardson 10 Smyth 5 Sorrel 6 15 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)
In this example string is a character column or string expression, starting character is the starting position of the substring, and number of characters is the number of characters to return.
As an example, if you want to obtain the first four characters of the customer's last name, the call to the SUBSTR function looks like this:
SQL> select Last_Name, substr(Last_Name,1,4) 2 from Customer 3 order by Last_Name; LAST_NAME SUBS ------------------------------ ---- Chen Chen Fleming Flem Fray Fray Hernandez Hern Horace Hora Jensen Jens Johnson John Kramden Kram Martinez Mart Mcclintock Mccl Moran Mora Pareski Pare Richardson Rich Smyth Smyt Sorrel Sorr 15 rows selected.
In addition to using literal values in the SUBSTR function, you can use a function as an argument in the SUBSTR function. For example, suppose you want to retrieve the last three characters of the customer'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 the following example, 3_1 = 2:
SQL> select substr(Last_Name,length(Last_Name)-2,3) 2 from Customer 3 order by Last_Name; SUB --- hen ing ray dez ace sen son den nez ock ran ski son yth rel 15 rows selected.
You learned how to use the LIKE operator in Chapter 6, "The Basics of 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.
To illustrate, if you want to retrieve all rows in which the Description column contains the pattern WATTS or RMS, you could use the LIKE function in the following query:
SQL> select Description 2 from Product 3 where 4 Description like '%WATTS%' or 5 Description like '%RMS%'; DESCRIPTION ------------------------------------------------------------------------ AMP 600 WATTS PER CHANNEL AMP, PROFESSIONAL 800W RMS PER CHANNEL PRE AMPLIFIER 150 WATTS/CHANNEL PREAMP, 460 W/RMS
A common data manipulation task is transforming one pattern into another in a particular column. Suppose you wanted to change the product description in the Product table so that all amplifiers were not abbreviated as AMP, but instead, spelled out as Amplifier. At the same time, you want to be careful not to transform a phrase like 50 AMPS to 50 AmplifierS.
Fortunately, Oracle provides a function, REPLACE, that manipulates a column by replacing one string with another string. The syntax for the REPLACE function is
REPLACE (string, existing_string, [replacement_string])
where string is a string expression, existing_string is a string that might occur in string, and replacement_string is an optional string with which to replace existing_string.
Here's how you can use the REPLACE function to change a product description in the Product table. First, display the current product descriptions.
SQL> select description from product; DESCRIPTION ------------------------------------------------ AMPLIFIER, 100W PER CHANNEL, RMS AMP 300W PER CHAN, RMS AMP 600 WATTS PER CHANNEL, RMS AMP, PROFESSIONAL 800W RMS PER CHANNEL Preamplifier, 200 W per channel, RMS PRE AMPLIFIER 150 WATTS/CHANNEL CD PLAYER, SINGLE-DISK 5-DISK CD PLAYER JUKEBOX, CD - 100 DISK CAPACITY Pre-amp, 120 W per channel Pre amp, 250 W/channel, RMS PREAMP, 460 W/RMS Tuner 14 rows selected.
Next, use an UPDATE statement to change occurrences of W per channel, RMS to W PER CHANNEL.
SQL> update Product 2 set Description = 3 replace(Description,'W per channel, RMS','W PER CHANNEL'); 2 rows updated. SQL> select Description 2 from Product; DESCRIPTION ----------------------------------------------------------------- AMPLIFIER, 100W PER CHANNEL, RMS AMP 300W PER CHAN, RMS AMP 600 WATTS PER CHANNEL, RMS AMP, PROFESSIONAL 800W RMS PER CHANNEL Preamplifier, 200 W PER CHANNEL PRE AMPLIFIER 150 WATTS/CHANNEL CD PLAYER, SINGLE-DISK 5-DISK CD PLAYER JUKEBOX, CD - 100 DISK CAPACITY Pre-amp, 120 W per channel Pre amp, 250 W/channel, RMS PREAMP, 460 W/RMS Tuner 14 rows selected.
If you don't specify a replacement string in the REPLACE function, the existing string is removed from the column. Using the same example, the following query displays the product descriptions:
SQL> select description from product; DESCRIPTION --------------------------------------------------------- AMPLIFIER, 100W PER CHANNEL, RMS AMP 300W PER CHAN, RMS AMP 600 WATTS PER CHANNEL, RMS AMP, PROFESSIONAL 800W RMS PER CHANNEL Preamplifier, 200 W PER CHANNEL PRE AMPLIFIER 150 WATTS/CHANNEL CD PLAYER, SINGLE-DISK 5-DISK CD PLAYER JUKEBOX, CD - 100 DISK CAPACITY Pre-amp, 120 W per channel Pre amp, 250 W/channel, RMS PREAMP, 460 W/RMS Tuner 14 rows selected.
Then the REPLACE function is used without a replacement string to eliminate all occurrences of the pattern RMS in the Description column.
SQL> update Product 2 set Description = 3 replace(Description,'RMS') 4 ; 14 rows updated.
As you can see in the following query, the pattern RMS has been removed from all product descriptions.
SQL> select Description from Product; DESCRIPTION --------------------------------------------------------- AMPLIFIER, 100W PER CHANNEL AMP 300W PER CHAN AMP 600 WATTS PER CHANNEL AMP, PROFESSIONAL 800W PER CHANNEL Preamplifier, 200 W PER CHANNEL PRE AMPLIFIER 150 WATTS/CHANNEL CD PLAYER, SINGLE-DISK 5-DISK CD PLAYER JUKEBOX, CD - 100 DISK CAPACITY Pre-amp, 120 W per channel Pre amp, 250 W/channel PREAMP, 460 W/ Tuner 14 rows selected.
If a character column contains leading or trailing blanks, a query based on a specified value for the column might return misleading results. To illustrate this point, modify the Product table so that the length of Manufacturer_ID is seven characters.
SQL> alter table product modify (manufacturer_id varchar2(7)); Table altered.
This modification enables you to add a blank to the end of a six-character Manufacturer_ID.
Now you can add a trailing blank to Manufacturer_ID whenever Manufacturer_ID is equal to SEN101--;the code used for Seny Electronics.
SQL> update product 2 set manufacturer_id = 'SEN101 ' 3 where 4 manufacturer_id = 'SEN101'; 6 rows updated.
Now if you query the table for products for which the Manufacturer_ID is equal to SEN101, Oracle won't return any records because 'SEN101' isn't equal to 'SEN101 '.
SQL> select Product_ID 2 from Product 3 where 4 Manufacturer_ID = 'SEN101'; no rows selected
Of course, if you hadn't been aware of the trailing blank, you would have been surprised by the fact that the query returned zero rows.
TipTo avoid misleading query results, remove leading and trailing blanks before a row is added to a table or modified. Trailing blanks cause more problems than leading blanks do because trailing blanks aren't as obvious.
Oracle provides two functions for trimming blanks: LTRIM and RTRIM. LTRIM trims a string's leading blanks, and RTRIM trims a string's trailing blanks.
To trim leading and trailing blanks from a string, simply embed the RTRIM function inside the LTRIM function. If no leading or trailing blanks occur, LTRIM and RTRIM won't modify the existing string. Look at the following example to see how the RTRIM function is used:
SQL> update Product 2 set Manufacturer_ID = RTRIM(Manufacturer_ID) 3 where 4 Manufacturer_ID like '% '; 6 rows updated.
Notice how the WHERE clause was used to update only those rows in which the Manufacturer_ID contained a trailing blank. RTRIM will trim trailing blanks 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 against which RTRIM is applied.
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, suppose you have a table named Test_Trim with a single VARCHAR2 column named MY_COL. You know that some rows have leading characters, and you want to trim off x, y, and z.
SQL> select my_col 2 from test_trim; MY_COL -------------------------------------------------------------- yzzxHello, world
zyxGoodbye, cruel world
Use LTRIM to remove the offending characters.
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
You can eliminate trailing characters by employing the same technique using RTRIM.
You can check for trailing blanks in several ways. One of the easiest methods uses the LIKE operator:
select Manufacturer_ID from Product where Manufacturer_ID like '% ';
This method certainly works, but concentrate on preventing leading and trailing blanks from being stored with your data in the first place. Here are two techniques you can use:
Undoubtedly, you've been 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)
where string is the literal string or string column to be left-padded, n is the total length of the string returned by LPAD, and pad_string is the string to left pad onto string.
In the following example, the LPAD function left pads blanks onto 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 blank 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. The following example demonstrates how you can add a fixed value to the LENGTH function as an argument to LPAD:
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
By increasing the number added to LENGTH, you can left pad the string with pad_string more than once, as shown next.
SQL> select lpad(my_col,length(my_col)+16,'You say ') from test_trim; 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 query that follows, 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. In the following example, the second argument for LPAD is 50. 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.
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. The next example uses the lengths of my_col and my_col2 as arguments to guarantee that left-padding occurs only once.
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
In the preceding SELECT statement, my_col is the first argument for LPAD. For the second argument, you add the length of my_col' 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 , .
RPAD works just like LPAD. The syntax for RPAD is
RPAD (string, n, pad_string)
where string is the literal string or string column to be right-padded, n is the number of times to right pad pad_string, and 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. In the following example, the first 15 characters of the product description are selected and then converted to uppercase with the UPPER function:
SQL> select substr(Description,1,15), 2 substr(upper(description),1,15) 3 from Product; SUBSTR(DESCRIPT SUBSTR(UPPER(DE --------------- --------------- AMPLIFIER, 100W AMPLIFIER, 100W AMP 300W PER CH AMP 300W PER CH AMP 600 WATTS P AMP 600 WATTS P AMP, PROFESSION AMP, PROFESSION Preamplifier, 2 PREAMPLIFIER, 2 PRE AMPLIFIER 1 PRE AMPLIFIER 1 CD PLAYER, SING CD PLAYER, SING 5-DISK CD PLAYE 5-DISK CD PLAYE JUKEBOX, CD - 1 JUKEBOX, CD - 1 Pre-amp, 120 W PRE-AMP, 120 W Pre amp, 250 W/ PRE AMP, 250 W/ PREAMP, 460 W/R PREAMP, 460 W/R Tuner TUNER 14 rows selected.
Similarly, you can use the LOWER function to convert all characters to lowercase.
SQL> select substr(Description,1,15), 2 substr(lower(Description),1,15) 3 from Product; SUBSTR(DESCRIPT SUBSTR(LOWER(DE --------------- --------------- AMPLIFIER, 100W amplifier, 100w AMP 300W PER CH amp 300w per ch AMP 600 WATTS P amp 600 watts p AMP, PROFESSION amp, profession Preamplifier, 2 preamplifier, 2 PRE AMPLIFIER 1 pre amplifier 1 CD PLAYER, SING cd player, sing 5-DISK CD PLAYE 5-disk cd playe JUKEBOX, CD - 1 jukebox, cd - 1 Pre-amp, 120 W pre-amp, 120 w Pre amp, 250 W/ pre amp, 250 w/ PREAMP, 460 W/R preamp, 460 w/r Tuner tuner 14 rows selected.
Finally, the INITCAP function will convert all characters to lowercase and capitalize the first letter of each word.
SQL> select substr(Description,1,15), 2 substr(initcap(Description),1,15) 3 from Product; SUBSTR(DESCRIPT SUBSTR(INITCAP( --------------- --------------- AMPLIFIER, 100W Amplifier, 100w AMP 300W PER CH Amp 300w Per Ch AMP 600 WATTS P Amp 600 Watts P AMP, PROFESSION Amp, Profession Preamplifier, 2 Preamplifier, 2 PRE AMPLIFIER 1 Pre Amplifier 1 CD PLAYER, SING Cd Player, Sing 5-DISK CD PLAYE 5-Disk Cd Playe JUKEBOX, CD - 1 Jukebox, Cd - 1 Pre-amp, 120 W Pre-Amp, 120 W Pre amp, 250 W/ Pre Amp, 250 W/ PREAMP, 460 W/R Preamp, 460 W/R Tuner Tuner 14 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.
In Chapter 5, "Your First Personal Oracle7 Database," you created a table that stores a condition code--;a code used to describe a piece of equipment brought in for repair--;and its description. But imagine for a minute that you don't have such a table.
If you want to create a repair report that lists the condition of each item brought in for repair, you can write a simple query. But if you give this report to someone who isn't familiar with the condition codes that the repair store uses, that individual is going to have difficulty understanding the report without some assistance.
Fortunately, the DECODE function serves this purpose. Its syntax is
DECODE (expression, value1, returned_value1, ... valueN, returned_valueN, [default_returned_value])
where expression is a valid Oracle expression, valueN is a possible value to which expression might be equal, and 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.
In the following example a technician at Frayed Wires assigns a condition code to each piece of equipment that a customer brings in for repair (see Table 10.1) and makes a corresponding entry into the Repair_Detail table.
Table 10.1. Condition codes.
Code Description
DS Dents and scratches ES Electrical short IP Intermittent problem MK Missing knobs MS Missing screws, connectors NG No good at all NP No power SC Some scratches X Excellent
The following listing shows the current contents of Repair_Detail:
SQL> select Repair_ID, Product_ID, Manufacturer_ID, Item_Number, Condition_Code
2 from Repair_Item
3 order by Repair_ID;
REPAIR_ID PRODUCT_ID MANUFA ITEM_NUMBER CON
--------- ------------ ------ ----------- ---
501 C2002 MIT501 1 SC
502 D301 SEN101 1 X
503 B311 TES801 1 MK
504 B801 SEN101 1 NG
505 A903 TES801 1 NP
506 TR901 TES801 1 IP
6 rows selected.
You can use the DECODE function to translate the encoded value. For example, the following query uses the DECODE function to return a description for the value of Condition_Code:
SQL> select Repair_ID, Product_ID, Manufacturer_ID, Item_Number,
2 decode(Condition_Code,
3 'DS','Dents and scratches',
4 'ES','Electrical short',
5 'IP','Intermittent problem',
6 'MK','Missing knobs',
7 'MS','Missing screws, connectors',
8 'NG','No good at all',
9 'NP','No power',
10 'SC','Some scratches',
11 'X', 'Excellent',
12 'Unknown condition code')
13 from Repair_Item
14 order by Repair_ID;
REPAIR_ID PRODUCT_ID MANUFA ITEM_NUMBER DECODE(CONDITION_CODE,'DS'
--------- ------------ ------ ----------- --------------------------
501 C2002 MIT501 1 Some scratches
502 D301 SEN101 1 Excellent
503 B311 TES801 1 Missing knobs
504 B801 SEN101 1 No good at all
505 A903 TES801 1 No power
506 TR901 TES801 1 Intermittent problem
6 rows selected.
At some point you may want to obtain the ASCII numeric equivalent of a character in a column. The ASCII function serves this purpose. It takes one string as its argument. ASCII returns the ASCII numeric equivalent of the first character of its argument.
SQL> select ASCII(Last_Name), Last_Name
2 from Customer
3 order by Last_Name;
ASCII(LAST_NAME) LAST_NAME
---------------- ------------------------------
67 Chen
70 Fleming
70 Fray
72 Hernandez
72 Horace
74 Jensen
74 Johnson
75 Kramden
77 Martinez
77 Mcclintock
77 Moran
80 Pareski
82 Richardson
83 Smyth
83 Sorrel
15 rows selected.
Many of Oracle's built-in string functions are the equivalent of functions available in programming and scripting languages. A good example is the INSTR function, which indicates the position of one string inside another string. The syntax for INSTR is
INSTR (string1,string2,[starting_position,[occurrence_number]])
where string1 is the string to be searched, string2 is the string to look for in string1, and starting_position is the position in string1 to begin the search. occurrence_number is the number of the occurrence of string2 in string1 for which you are looking.
INSTR returns the position of string2 in string1 based on the supplied values for starting_position and occurrence_number. If string2 cannot be found in string1 based on the other arguments, INSTR returns a zero.
In the following code fragment, the INSTR function finds the first occurrence of an in my_col:
SQL> select my_col, instr(my_col,'an') 2 from test_trim; MY_COL INSTR(MY_COL,'AN') ------------------------------ ------------------ California 0 Michigan 7 Washington 0 Oregon 0 Louisiana 7 Illinois 0 Albany 4 Pennsylvania 9 Arizona 0 Nevada 0 Montana 5 North Dakota 0 12 rows selected.
The following are some of the string functions and operators that you can use in Personal Oracle7: