Oracle Magazine - July/August 2012

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;

FIRST_NAME LENGTH ————————————————————————————————————————————— —————————————— Frances 7 Matthew 7 michael 7 Donald 6 Betsy 5 Emily 5 Roger 5 mark 4

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',

1, 2)

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:

CONCLUSION

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 details: bit.ly/HZUBC5.

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
PL/SQL Practices
for Oracle Developers
and DBAs (Apress, 2011) and Expert Oracle
Practices: Oracle Database Administration from
the Oak Table (Apress, 2010).

NEXT STEPS

INSTR('MISSISSIPPI','ISSI', 1, 2) ——————————————————————————————————

5

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