Home arrow Oracle arrow Page 6 - Mastering the WHERE Clause

Matching Conditions - Oracle

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.)

  1. Mastering the WHERE Clause
  2. WHERE to the Rescue
  3. WHERE Clause Evaluation
  4. Conditions and Expressions
  5. Membership Conditions and Range Conditions
  6. Matching Conditions
  7. Regular Expressions and Handling NULL
  8. Placement of Join Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 22
October 19, 2004

print this article



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:

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:

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”:

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.  

Buy the book!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!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: