Suppose variable s has been assigned in this way: s = \'\'\'It was the best of t

Question

Suppose variable s has been assigned in this way: s = '''It was the best of times, it was the worst of times; it was the age of wisdom, it was the age of foolishness; it was the epoch of belief, it was the epoch of incredulity; it was ...''' (The beginning of A Tale of Two Cities by Charles Dickens.) Then do the following, in order: (a) Writeasequenceofstatementsthatproduceacopyofs,namednewS,inwhichchar- acters ., ,, ;, and have been replaced by blank spaces. (b) Remove leading and trailing blank spaces in newS (and name the new string newS). (c) Make all the characters in newS lowercase (and name the new string newS). (d) Compute the number of occurrences in newS of string 'it was'. (e) Change every occurrence of was to is (and name the new string newS). (f) Split newS into a list of words and name the list listS

Explanation / Answer

The most-basic character functions enable you to change the way alphanumeric data is formatted in a result set. For example, the simple query in Listing 1 obtains all unique last name values from the EMPLOYEE table and displays them in all capital letters. It does this by applying the UPPER character function to the LAST_NAME column. Similarly, the query in Listing 2 uses the LOWER character function to display all department location names from the DEPARTMENT table in lowercase letters. All functions take some kind of input parameter(s). Character functions require input parameters that are alphanumeric—either a character (or string) literal or a column with a VARCHAR2, CHAR, or CLOB datatype. The data in the EMPLOYEE table’s LAST_NAME column and the DEPARTMENT table’s LOCATION column is stored with a datatype of VARCHAR2. Recall that a literal character value is any list of alphanumeric characters enclosed in single quotation marks, such as ‘Smith’, ‘73abc’, or ‘15-MAR-1965’.

Code Listing 1: Query that lists every unique last name value in uppercase letters

Code Listing 2: Query that displays all department locations in lowercase letters

Listings 3 and 4 demonstrate the INITCAP function. The query in Listing 3 uses INITCAP to convert certain first and last names from being stored in all lowercase in the EMPLOYEE table to being displayed with initial capital letters. The INITCAP function capitalizes the first letter of a string andlowercases the remainder of the string,

Compute the number of occurrences:

When you need to search column values for similar string pattern values, you can do so with the INSTR character function. INSTR—which stands for in string—returns the position of a substring within a string value. Listing 11 demonstrates the INSTR function applied to the LAST_NAME column of the EMPLOYEE table to locate all occurrences of the “ton“ substring. As you can see, the INSTR function takes as input the literal or column value you want to search, followed by the substring pattern to search for. In Listing 11, the INSTR function finds the “ton“ pattern in only two column data values—both of them Newton—and returns 4 as their position. Because it did not find the search string in any other values, the output for those values is 0.

Code Listing 11: Query that demonstrates the INSTR character function

Two additional parameters—starting position and occurrence—are optional. The starting position specifies the character in the string from which to begin your search. The default behavior is for the search to begin at the first character—otherwise known as character position 1. The occurrence parameter lets you specify which occurrence of the substring you’d like to find. For example, the word Mississippi includes two occurrences of the “issi“ substring. To search for the starting-position location of the second occurrence of this pattern, you must provide the INSTR function with an occurrence parameter of 2:

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.