Oracle Magazine - July/August 2012

tion to the LAS T_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 LAS T_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’.

Listings 3 and 4 demonstrate the INI TCAP 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 INI TCAP function capitalizes the first letter of a string and lowercases the remainder of the string, as demonstrated by the query in Listing 4. That query also shows that the input parameter for an INITCAP function can consist of a character function’s application to a string or a database column that stores alphanumeric data. Specifically, the query applies the UPPER function to the LAST_NAME column of the EMPLOYEE table for certain employees. The UPPER function is said to be nested inside the INI TCAP function. The Oracle Database server applies nested functions in order, from innermost function to outermost function. In Listing 4, the UPPER function converts the values peterson and leblanc to PETERSON and LEBLANC. Then the INITCAP function converts those uppercase values to Peterson and Leblanc.

Code Listing 4: Query that demonstrates the INITCAP function

SQL> select INITCAP('eMPLOYEE lAST nAMES') "INITCAP Literal", INITCAP(UPPER(last_name)) "Converted Employee Last Name"

2 from employee

3 where employee_id in (6569, 6570);

INITCAP Literal Converted Employee Last Name ———————————————————————————— —————————————————————————————————————————— Employee Last Names Peterson Employee Last Names Leblanc

2 rows selected.

Code Listing 5: Query that applies the RPAD and the LPAD functions

SQL> select RPAD(name, 15, '.') department, LPAD(location, 15, '.') location 2 from department;

DEPARTMENT LOCATION ————————————————————— —————————————————————— Accounting..... ....LOS ANGELES Payroll........ .......NEW YORK

2 rows selected.

Code Listing 6: Query that demonstrates the CONCAT function

SQL> select CONCAT(first_name, last_name) employee_name 2 from employee

3 order by employee_name;

EMPLOYEE_NAME —————————————————————————————————————————————————————————————————————————————————— BetsyJames DonaldNewton EmilyEckhardt FrancesNewton MatthewMichaels RogerFriedli markleblanc michaelpeterson

8 rows selected.

long, counting the space, the LPAD function adds four filler characters to its left.

PADDING YOUR RESULTS

To pad something is to add to it. The LPAD and RPAD functions enable you to pad your character-data results by repeating a character, space, or symbol to the left or right of any string. LPAD pads to the left of a string; RPAD pads to the right.

Listing 5 demonstrates the power of the
RPAD and LPAD functions. Note that each
takes three input parameters: the column
name or string literal you want to pad; the
length to which the string should be padded;
and the character, space, or symbol (the
filler) to pad with. For example, the query in
Listing 5 specifies that the department name
should be right-padded to a total length of
15 with the “.” filler character. If any depart-
ment name is exactly 15 characters or longer,
no filler character will be added. Because
Accounting is 10 characters long, the RPAD
function adds five filler characters to its
right. The query also specifies that the loca-
tion should be left-padded to a total length
of 15. Because LOS ANGELES is 11 characters

THE HELPFUL DUAL

Oracle Database provides a single-row, single-column table called DUAL that is useful for many purposes, not the least of which is learning about Oracle functions. DUAL is an Oracle system table owned by the SYS user, not the SQL_101 schema. Many Oracle system tables are made available to all users via public synonyms. Synonyms will be discussed in subsequent articles in this series.