Matching Conditions - Oracle

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! Visit the O'Reilly Network http://www.oreillynet.com for more online content.