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.
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.
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 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) 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.
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.
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:
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.
Gender Last Name Office-Caps Office-InitCap
------ --------------- ----------- --------------
m SIMMONS SW4801 Sw4801
f BOUDREAUX NW0105 Nw0105
m ADAMS NW0105 Nw0105
more rows will be displayed…
The general form of the LENGTH function is:
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 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"
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.
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 */
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:
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 */
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:
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:
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.
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 CharacterSet is not specified, then the LTRIM and RTRIM functions trim blank spaces from the CharacterString. The format for each of these functions is:
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 (
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 */
March 20 5
You can also use the LTRIM and RTRIM functions to trim unwanted characters from column values. For example, if some data values stored in the Address column of the patient table have been stored with a period at the end of the address, it may be desirable to trim this period prior to displaying a result set. The expression to trim the period from the right end of the column values is shown below. Notice that the character string to be trimmed is given inside single quote marks.
SQL Example 10.8 provides an example of using the TRIM function. The query trims the leading zero from the DateHired column of the employee table. The specified character to trim is the zero.
/* SQL Example 10.8 */
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"
WHERE DepartmentNumber = 4;
EMPLOYEEID Untrimmed Date Trimmed Date
---------- -------------- --------------
66432 01-FEB-84 1-FEB-84
66444 03-JAN-96 3-JAN-96
The 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'.
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 */