Oracle Magazine - July/August 2012

Database Application Developer and DBA
SQL 101 BY MELANIE CAFFREY

A Function of Character
Part 6 in a series on the basics of the relational
database and SQL
ORACLE DATABASE

Part 5 in this series, “An Order of Sorts” (Oracle Magazine, May/June 2012), introduced the ORDER BY clause of a SQL SELECT statement (or query) and how it behaves in conjunction with certain options and keywords to order (or sort) the data in query results. Now you are ready to start learning how to use SQL functions in your queries to transform result set data so that it displays differently from how it is stored in the database. This article focuses on the SQL character functions (also known as string functions or text functions), which enable you to manipulate how character data is displayed. Subsequent articles in this series will introduce the date and number functions.

To try out the examples in this and subsequent articles in the series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system from bit.ly/fherki. I recommend installing Oracle Database, Express Edition.

If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_101 is the user account you’ll use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and S YS TEM and make a note of them.

I-HUA CHEN

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance— download, unzip, and execute the SQL script available at bit.ly/JsDOX2 to create the tables for the SQL_101 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)

PRE T T Y PRINTING

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 func-

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

SQL> set feedback on SQL> select distinct UPPER(last_name) "Uppercase Employee Last Name"

2 from employee 3 order by UPPER(last_name);

Uppercase Employee Last Name ———————————————————————————————————————————— ECKHARDT FRIEDLI JAMES LEBLANC MICHAELS NEWTON PETERSON

7 rows selected.

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

SQL> select name, LOWER(location) "Lowercase Department Location" 2 from department

3 order by location;

NAME Lowercase Department Location —————————————————————————————————————— ———————————————————————————————————————————— Accounting los angeles Payroll new york

2 rows selected.

Code Listing 3: Query that shows certain names converted and with the initial letter capitalized

SQL> set lines 32000 SQL> select first_name, last_name, INITCAP(first_name) "First Name", INITCAP(last_name) "Last Name"

2 from employee 3 where employee_id in (6569, 6570);

FIRST_NAME LAST_NAME FirstName LastName —————————————— ————————————— —————————————— ————————————— michael peterson Michael Peterson mark leblanc Mark Leblanc