Additional functions
Yüklə 227 Kb.
|
- Bu səhifədəki naviqasiya:
- Objectives
- General notation
- CHARACTER (String) Functions
- UPPER, LOWER, and INITCAP Functions
- LENGTH Function
- SUBSTR and CONCAT Functions, and Concatenation
- LTRIM, RTRIM, and TRIM Functions
- INSTR Function
| Chapter 10 CHAPTER 10--------------------------------------------------------------------------------------------------------------------- ADDITIONAL FUNCTIONSChapter 5 covered the use of numeric, aggregate functions such as AVG, MAX, and MIN. Oracle has many additional functions. Some of these functions are used in writing procedural SQL (PL/SQL) program code for stored procedure and trigger scripts that you will learn later in this text. However, there are numerous functions that are used for nonprocedural SELECT statements. These functions can provide additional capabilities that add power to your queries, and this makes life easier for you as an SQL programmer. This chapter focuses on a subset of Oracle functions that are used quite often with SELECT statements. To help you learn the functions, we have classified them according to their use as: (1) character, (2) mathematical, (3) conversion, (4) datetime and (5) the specialized DECODE function. ObjectivesCharacter functions allow you to manipulate a string of characters that can include letters, numbers, and special characters. These functions also allow you to examine data stored in columns defined as various CHAR and VARCHAR2 data types. Mathematical functions, as the name implies, manipulate values stored in tables defined as NUMBER columns. You will also study conversion functions that convert values from one data type to another, such as character to date and date to character. Datetime functions are used primarily to manipulate data stored in DATE columns of tables. Remember, a DATE column stores both date and time information so date functions also enable you to manipulate the time values stored in these columns. Additionally, the DECODE function is a form of If-Then-Else function that enables value-by-value substitutions of data stored in table columns. Your learning objectives are:
General notationAs discussed in Chapter 5, the general notation for functions is as follows: FUNCTION (argument1 [,option]) The function name is given in capital letters. The parameter argument1 is a placeholder that is filled by either a string of characters enclosed in single-quote marks, a column name, or a numeric value. As is the case with aggregate functions, each function has a single set of parentheses, and all arguments and options are enclosed by these parentheses. The optional clauses vary among the different functions. CHARACTER (String) FunctionsCharacter functions are classified in Oracle technical manuals as single-row functions. A single-row function returns a row to the result table for every row of a table that is queried. While the use of functions is quite flexible in Oracle, we will concentrate on teaching you to write SELECT statements with functions used in the SELECT, WHERE, and HAVING clauses. Character functions manipulate character strings. Quite simply, a character string refers to a group of characters where the characters can be alphabetic letters, numbers, spaces, and special characters. Examples of character string values are shown in Table 10.1. Table 10.1
Did you notice that numeric values are treated as character strings if they are not manipulated mathematically? For example, you would never add two telephone numbers together, nor would you subtract two product numbers; therefore, telephone numbers and product numbers are actually character strings—not numbers. Some character functions return character values while others return numeric values. If the argument that is input to the function is of data type CHAR or VARCHAR2, the function returns a value of data type VARCHAR2. When you are working with applications that are used world-wide, character data is often stored using the NCHAR and NVARCHAR2 data types. When the function input argument is NCHAR or NVARCHAR2, the function returns a value of data type NVARCHAR2. If the length of the return value exceeds the specified limit for data storage, Oracle will truncate the return value without displaying an error message. The limit for data storage depends on the maximum length of the data type specified. Table 10.2 summarizes the character functions that you will examine in this section. The functions are listed alphabetically; however, we have grouped the functions according to the task they perform in our coverage to aid you in learning their use. Table 10.2
UPPER, LOWER, and INITCAP FunctionsThe UPPER, LOWER, and INITCAP functions can alter the appearance of information displayed in a result table. The UPPER function converts data stored in a character column to upper case letters. The LOWER function, on the other hand, converts data stored in a character column to lower case letters. The INITCAP function capitalizes the first letter of a string of characters. The general form of these functions is: LOWER(CharacterString) UPPER(CharacterString) INITCAP(CharacterString) The query in SQL Example 10.1 selects data from the employee table. The Gender column stores a single-character coded value of "M" for male or "F" for female, and these values are stored in capitalized format. The first expression in the SELECT clause uses the LOWER function to display these coded values in lower case. The second expression in the SELECT clause uses the UPPER function to display employee last names as all capital letters. Each employee's office number is actually not a number; rather, the office number is a string of characters that combine capital letters and numbers, such as "SW4801" for the southwest wing, 4th floor of the hospital. If it is desirable to display these values with only the first letter capitalized, the LOWER function can first return the lowercase equivalents of these alphabetic characters that are part of the office number. By nesting the LOWER function inside the INITCAP function, the lowercase equivalents will be displayed with just the first letter capitalized. /* SQL Example 10.1 */ COLUMN "Gender" FORMAT A6; COLUMN "Last Name" FORMAT A15; COLUMN "Office-Caps" FORMAT A11; COLUMN "Office-InitCap" FORMAT A14; SELECT LOWER(Gender) "Gender", UPPER(LastName) "Last Name", Office "Office-Caps", INITCAP(LOWER(Office)) "Office-InitCap" FROM Employee;
------ --------------- ----------- -------------- m SIMMONS SW4801 Sw4801 f BOUDREAUX NW0105 Nw0105 m ADAMS NW0105 Nw0105
The SELECT statement in SQL Example 10.2 produces a result table listing the cities where patients of the Madison Hospital reside. It also specifies the numeric length of each city name. Note that blank characters count as part of the length. Additionally, since the City column is defined as VARCHAR2, the field is not blank-padded. /* SQL Example 10.2 */ COLUMN "City" FORMAT A15; COLUMN "Length" FORMAT 999999; SELECT DISTINCT City "City", LENGTH(City) "Length" FROM Patient;
--------------- ------- Alton 5 Collinsville 12 Edwardsville 12 O Fallon 8 SUBSTR and CONCAT Functions, and ConcatenationThe SUBSTR function is a very powerful function that can extract a substring from a string of characters. The general format of the function is: SUBSTR(CharacterString, StartPosition [, NumberOfCharacters]) The CharacterString parameter is the string value from which you wish to extract characters. The StartPosition parameter specifies the position within the string with which to begin the extraction. For example, a specification of 4 for StartPosition would start the extraction with the 4th character. The optional parameter NumberOfCharacters specifies how many characters to extract. When this parameter is not specified, the extraction automatically continues to the last character in the string. The SELECT statement in SQL Example 10.3 extracts the last four digits of each employee's Social Security number (SSN) for display in a result table for department 3. /* SQL Example 10.3 */ COLUMN "Last Name" FORMAT A15; COLUMN "First Name" FORMAT A15; COLUMN "Last 4 SSN" FORMAT A10; SELECT LastName "Last Name", FirstName "First Name", SUBSTR(SSN,6) "Last 4 SSN" FROM Employee WHERE DepartmentNumber = 3; Last Name First Name Last 4 SSN --------------- --------------- ---------- Sumner Elizabeth 3308 Becker Robert 9991 Jones Quincey 9993 Barlow William 9002 Smith Susan 5540
COLUMN "Employee Name" FORMAT A30; COLUMN "SSN" FORMAT A12; SELECT LastName || ', ' || FirstName "Employee Name", SUBSTR(SSN,1,3) ||'-' || SUBSTR(SSN,4,2) || '-' || SUBSTR(SSN,6,4) "SSN" FROM Employee WHERE DepartmentNumber = 3; Employee Name SSN ------------------------------ ------------ Sumner, Elizabeth 216-22-3308 Becker, Robert 347-88-9991 Jones, Quincey 654-33-9993 Barlow, William 787-24-9002 Smith, Susan 548-86-5540
The SSN is formatted by using the first SUBSTR function to extract the first three numbers from the employee SSN character string beginning in character position 1. The concatenation operator appends these three numbers to a dash (-) symbol. Another concatenation operator appends a second SUBSTR function that extracts the next two numbers of the SSN. Another set of concatenation operators append another dash, and then the third SUBSTR extracts the last four digits of the SSN. An alias column name of "SSN" is assigned to this expression for use in the COLUMN command that formats the expression as A12. You'll also note that this particular expression is quite long. SQL allows you to break the expression to start a new line—a convenient break point is right before or after the use of a concatenation operator. You can also concatenate strings by using the CONCAT function. The general format of the function is:
SELECT CONCAT(CONCAT(LastName, ', '), FirstName) "Employee Name", CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(SSN, 1, 3), '-'), SUBSTR(SSN, 4, 2)), '-'), SUBSTR(SSN, 6, 4)) "SSN" FROM Employee WHERE DepartmentNumber = 3; Employee Name SSN ------------------------------ ------------ Sumner, Elizabeth 216-22-3308 Becker, Robert 347-88-9991 Jones, Quincey 654-33-9993 Barlow, William 787-24-9002 Smith, Susan 548-86-5540
Becker,
Jones, Barlow,
Smith, The second CONCAT function concatenates the initial employee name with the FirstName column values. This creates the complete string value displayed under the Employee Name column heading as follows: Sumner, Elizabeth Becker, Robert Jones, Quincey Barlow, William Smith, Susan
The format for LTRIM and RTRIM is very simple. The character value (CharacterString) to be trimmed is specified along with an optional set of characters to trim (CharacterSet). If CharacterSet is not specified, then the LTRIM and RTRIM functions trim blank spaces from the CharacterString. The format for each of these functions is: RTRIM(CharacterString [,'CharacterSet']) LTRIM(CharacterString [,'CharacterSet']) The format for the TRIM function is a bit more complex than RTRIM and LTRIM, but it is still easy to use. The format is: TRIM([Leading, Trailing, Both] 'Character' FROM CharacterString) The optional keywords LEADING, TRAILING, and BOTH can be used to specify how to trim a string. For example, when you specify the keyword LEADING, Oracle removes leading characters from CharacterString that are equal to the Character specified. Similarly, the TRAILING keyword specifies to trim from the trailing end of CharacterString, while the keyword BOTH (or not specifying any of these three) trims both ends of a string. As noted earlier, if you do not specify the trim character, the default trim character is the blank space. If you only specify CharacterString, then Oracle will trim blank space from the leading and trailing ends of a string. The string returned by the function is a VARCHAR2 data type. When CharacterString = NULL, the TRIM function returns NULL. Let's examine some examples in order to clarify these rules about trimming strings. Suppose that a data table has a fixed-length column defined as CHAR(20), and that this column stores values that actually vary in length, such as the names of the month in a year. Further, suppose that there is a need to compute the length of each value stored in this column. Since CHAR data fields are automatically blank-padded when values are not sufficiently large to fill up all of the column space, the LENGTH function would return a length of 20 for each column value! However, you can combine the RTRIM function with the LENGTH function to return a true length of the values stored in the column. Let's test this by creating and populating a table named monthTable with three rows of data as shown in SQL Example 10.6. /* SQL Example 10.6 */ CREATE TABLE MonthTable ( MonthName CHAR(20));
INSERT INTO MonthTable VALUES ('February'); INSERT INTO MonthTable VALUES ('March');
SELECT MonthName "Month", LENGTH(MonthName) "Untrimmed", LENGTH(RTRIM(MonthName)) "Trimmed" FROM MonthTable; Month Untrimmed Trimmed -------------------- ---------- ---------- January 20 7 February 20 8 March 20 5
COLUMN "Employee ID" FORMAT A11; COLUMN "Untrimmed Date" FORMAT A14; COLUMN "Trimmed Date" FORMAT A14; SELECT EmployeeID, DateHired "Untrimmed Date", TRIM(LEADING 0 FROM DateHired) "Trimmed Date" FROM Employee WHERE DepartmentNumber = 4; EMPLOYEEID Untrimmed Date Trimmed Date ---------- -------------- -------------- 66432 01-FEB-84 1-FEB-84 66444 03-JAN-96 3-JAN-96 INSTR FunctionThe INSTR function is used to search a character string for the existence of a character substring. The general format is: INSTR(CharacterString, SubString [, StartPosition [, Occurrence]]) The INSTR function returns a numeric value specifying the position within the CharacterString where the SubString begins. By default, the search for a substring begins at character position 1; however, you can specify a different start position to begin the search. Optionally, you can specify a search that finds substrings that occur more than once in a string. By specifying a numeric value for occurrence, such as 2, you can return the numeric value specifying where the second occurrence of a substring begins. A value of zero is returned if the specified SubString is not found within CharacterString. Suppose that you need to know if any patient lives at a street address that includes the word 'Main'. The SELECT statement in SQL Example 10.9 produces a result table listing employee addresses and the position where the word "Main" begins within the address. When the Position column (the second column) displays zero this means that the address does not contain the word 'Main'. /* SQL Example 10.9 */ COLUMN "Address" FORMAT A35; COLUMN "Position" FORMAT 99999999; SELECT Address "Address", INSTR(Address,'Main') "Position" FROM Patient;
----------------------------------- --------- 100 East Aardvark Avenue 0 Route #5, Box 4100 0 Southwest Clarice Drive 0 South Davison Drive 0 400 Main St. 5 401 Main St. 5 more rows will be displayed… Now the above query works, but managers are more likely to want a listing that is restricted to just patients with the word "Main" in the street address. We can use the INSTR function in the WHERE clause of a SELECT statement as shown in SQL Example 10.10 to produce the desired result table where the value returned by the INSTR clause is not equal to zero. /* SQL Example 10.10 */ COLUMN "Last Name" FORMAT A15; COLUMN "Address" FORMAT A20; SELECT LastName "Last Name", Address "Address" FROM Patient WHERE INSTR(Address,'Main') != 0; Last Name Address --------------- -------------------- Earnhardt 400 Main St. Franken 401 Main St. Young 505 Main St. Zebulon 5105 South Main St. Jupiter 42 Main St. Mousseau 53 Main St. Lakeside 54 Main St. Crawford 100 Main St. Howard 15 Main St. 9 rows selected. Yüklə 227 Kb. Dostları ilə paylaş:
|