Chapter 10
CHAPTER 10
------
ADDITIONAL FUNCTIONS
Chapter 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.
Objectives
Character 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:
- Use character functions to manipulate CHAR type data.
- Use mathematical functions to manipulate NUMBER type data.
- Use date functions to manipulate DATE type data.
- Use conversion functions to convert data from one data type to another data type.
- Use the DECODE function to complete value substitutions.
General notation
As 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 argument1is 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) Functions
Character 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
Type of Data to Be Stored / Example ValuesCustomer street address / 100 South Main Street
Customer telephone number / (618) 555-1212
Customer name / Bijoy Bordoloi or Douglas Bock
Social Security number / 999-99-9999
Product number / 13496
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
Function / Use/DefinitionCONCAT / Concatenates two substrings to return a single string. This function is equivalent to the concatenation operator (||).
INITCAP / Capitalizes the first letter of a string of characters
INSTR / Searches a character string for a character string subset and returns the start position and/or occurrence of the substring
LENGTH / Returns a numeric value equivalent to the number of characters in a string of characters
LOWER / Returns a character value that is all lower case
LTRIM / Trims specified characters from the left end of a string
RTRIM / Trims specified characters from the right end of a string
TRIM / Trims characters from both ends of a string.
SUBSTR / Returns a string of specified length from a larger character string beginning at a specified character position
UPPER / Returns a character value that is all uppercase
UPPER, LOWER, and INITCAP Functions
The 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;
Gender Last Name Office-Caps Office-InitCap
------
m SIMMONS SW4801 Sw4801
f BOUDREAUX NW0105 Nw0105
m ADAMS NW0105 Nw0105
more rows will be displayed…
LENGTH Function
The general form of the LENGTH function is:
LENGTH(CharacterString)
This function returns a numeric value equivalent to the number of characters that comprise a specified CharacterString. This function is usually used in conjunction with other functions for tasks such as determining how much space needs to be allocated for a column of output on a report. You have learned that Oracle stores strings of character data using the CHAR or VARCHAR2 data types. CHAR columns are fixed-length and Oracle blank-pads, that is, adds blanks to character strings that do not completely fill a CHAR column. VARCHAR2 columns are variable length. Thus, the column data type can affect the value returned by the LENGTH function.
The SELECT statement in SQL Example 10.2 produces a result table listing the cities where patients of the MadisonHospital 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;
City Length
------
Alton 5
Collinsville 12
Edwardsville 12
O Fallon 8
SUBSTR and CONCAT Functions, and Concatenation
The 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
Note that the number of characters to extract is not specified in SQL Example 10.3; thus, the extraction begins with the 6th character to the end of the string. The SUBSTR function specification can also be written as follows to specify that the last four digits of the SSN are to be extracted.
SUBSTR(SSN,6, 4) "Last 4 SSN"
The SUBSTR function can be combined with the concatenation operator ( | | ). The concatenation operator in SQL is two vertical lines. This enables you to concatenate substrings in order to achieve special formatted output. Recall that employee SSNs (SSN column) are stored in the employee table without the dashes used normally when displaying these values. SQL Example 10.4 demonstrates formatting of employee SSN values in the result table. The concatenation operator is also used to format each employee name (last and first name) for display as a single column.
/* SQL Example 10.4 */
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 SELECT clause concatenates each employee last name with a comma and blank space ', '. This is, in turn, concatenated to the employee first name.
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:
CONCAT(CharacterString1, CharacterString2)
In order to concatenate more than two strings, you must nest CONCAT functions. SQL Example 10.5 uses two nested CONCAT function to combine the employee last name, a comma and blank space, and the employee first name for display in the result table as a single column. Only employees from department 3 are included in the result table.
/* SQL Example 10.5 */
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
The first CONCAT function concatenates the values of the LastName column with a comma and space. This creates an initial employee name string value for each employee with output as follows:
Sumner,
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
SQL Example 10.5 displays the SSN column by breaking each employee's SSN value into three substrings by using three SUBSTR functions. These are, in turn, nested within multiple CONCAT functions to produce a formatted SSN output column as was done with the Employee Name column values. Work through the nested CONCAT functions on your own to ensure that you understand how the SSN value is formatted. The decision to use the concatenation operator ( || ) or the CONCAT function is up to you.
LTRIM, RTRIM, and TRIM Functions
The LTRIM and RTRIM functions trim characters from the left and right ends of strings, respectively. The TRIM function allows you to trim a specified character from either the leading end, trailing end, or both ends of a string.
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 CharacterSetis 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 ('January');
INSERT INTO MonthTable VALUES ('February');
INSERT INTO MonthTable VALUES ('March');
The SELECT statement in SQL Example 10.7 displays the month names, length of the untrimmed month names, and length of the trimmed month names. The RTRIM function is enclosed in the LENGTH function to produce the third output column.
/* SQL Example 10.7 */