Code Listing 14: Query that demonstrates the LENGTH function
SQL> select first_name, LENGTH(first_name) length
2 from employee
3 order by length desc, first_name;
a query that uses the LENGTH function to
display the length of all FIRST_NAME values
from the EMPLOYEE table.
The online version of this article at bit.ly/
JAQPk3 includes examples of LENGTH and
other character functions in WHERE and
ORDER BY clauses.
8 rows selected.
SQL> select INSTR('Mississippi', 'issi',
2 from dual;
you with this task. Listing 12 shows a query
that uses the SUBSTR function to extract the
first three characters of every LAST_NAME
value from the EMPLOYEE table. The SUBSTR
function takes two required parameters
and one optional input parameter. The first
parameter is the literal or column value on
which you want the SUBS TR function to
operate. The second parameter is the position of the starting character for the substring, and the optional third parameter is
the number of characters to be included in
the substring. If the third parameter is not
specified, the SUBSTR function will return
the remainder of the string.
Listing 13 demonstrates the SUBS TR and
INS TR functions working together to display
the portion of every LAS T_NAME value from
the EMPLOYEE table that contains the “ton”
substring. In this example, the output from
the INS TR function provides the value for the
input parameter that specifies the position
for the SUBSTR function’s starting character.
In the LAST_NAME values in which the substring “ton” is not found, the entire LAS T_
NAME value is returned, for two reasons:
SUBSTR treats a starting position of 0 the
same as a starting position of 1 (that is, as the
first position in the string), and because the
query omits the optional length parameter,
the full remainder of the string is returned.
function takes as input the literal or column
value you want to search, followed by the
substring pattern to search for. In Listing 11,
the INS TR function finds the “ton” pattern in
only two column data values—both of them
Newton—and returns 4 as their position.
Because it did not find the search string in any
other values, the output for those values is 0.
Two additional parameters—starting
position and occurrence—are optional. The
starting position specifies the character in
the string from which to begin your search.
The default behavior is for the search to
begin at the first character—otherwise
known as character position 1. The occurrence parameter lets you specify which
occurrence of the substring you’d like to find.
For example, the word Mississippi includes
two occurrences of the “issi” substring. To
search for the starting-position location of
the second occurrence of this pattern, you
must provide the INSTR function with an
occurrence parameter of 2:
This article has shown you how character
functions can be used in SELECT statements
to manipulate the ways data is displayed.
You’ve seen how to convert data values to
uppercase, lowercase, and mixed cases and
how to search for strings within strings.
You’ve also seen how to pad and trim data
and how to specify a string’s total length.
By no means does this article provide an
exhaustive list of the Oracle character functions. Review the documentation for more
The next installment of SQL 101 will
discuss number functions and other miscellaneous functions.
Melanie Caffrey is
a senior development
manager at Oracle. She
is a coauthor of Expert
for Oracle Developers
and DBAs (Apress, 2011) and Expert Oracle
Practices: Oracle Database Administration from
the Oak Table (Apress, 2010).
INSTR('MISSISSIPPI','ISSI', 1, 2)
1 row selected.
EX TRACTING STRINGS FROM STRINGS
Sometimes you need to extract a portion of
a string for your desired output. The SUBS TR
(for substring) character function can assist
WHEN SIZE MAT TERS
Occasionally you need to determine a
string’s length—for example, to determine
the maximum number of characters a form
entry field should permit. Listing 14 shows
online-only article content
SQL 101, Parts 1–5
READ more about relational database
design and concepts
Oracle Database Concepts 11g
Release 2 ( 11. 2)
Oracle Database SQL Language Reference 11g
Release 2 ( 11. 2)
Oracle SQL Developer User’s Guide Release 3. 1
DOWNLOAD the sample script for