Gain the full power of SQL to write queries in an Oracle environment with this updated book (new information on Oracle 10g). This chapter focuses on the role of the WHERE clause in SQL statements and the various options available when building a WHERE clause. (Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu, O'Reilly, ISBN: 596006322.)
When dealing with character data, there are some situations where you are looking for an exact string match, and others where a partial match is sufficient. For the latter case, you can use the LIKE operator along with one or more pattern-matching characters, as in:
DELETE FROM part WHERE part_nbr LIKE 'ABC%';
The pattern-matching character % matches strings of any length, so all of the following part numbers would be deleted: 'ABC', 'ABC-123', 'ABC9999999'. If you need finer control, you can use the underscore (_) pattern-matching character to match single characters, as in:
DELETE FROM part WHERE part_nbr LIKE '_B_';
For this pattern, any part number composed of exactly three characters with a B in the middle would be deleted. Both pattern-matching characters may be utilized in numerous combinations to find the desired data. Additionally, the NOT LIKE operator may be employed to find strings that don’t match a specified pattern. The following example deletes all parts whose name does not contain a Z in the third position followed later by the string “T1J”:
DELETE FROM part WHERE part_nbr NOT LIKE '__Z%T1J%';
Oracle provides a slew of built-in functions for handling character data that can be used to build matching conditions. For example, the condition part_nbr LIKE 'ABC%' could be rewritten using the SUBSTR function as SUBSTR(part_nbr, 1, 3) = 'ABC'. For definitions and examples for all of Oracle’s built-in functions, see Oracle in a Nutshell (O’Reilly).
You may come across data that include the characters % and _ and need to include them in your patterns. For example, you might have a column called instructions in the cust_order table that may have a value such as:
Cancel order if more than 25% of parts are unavailable
If you want to find strings containing the % character, you will need to escape the % character within your pattern so that it isn’t treated as a wildcard. To do so, you will need to use the ESCAPE clause to let Oracle know which character you have chosen as the escape character:
SELECT instructions FROM cust_order WHERE instructions LIKE '%\%%' ESCAPE '\';
This query would return all rows where the instructions column contains the % character anywhere in the string.
If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!