HomeOracle Retrieving Table Data with the LIKE Operator
Retrieving Table Data with the LIKE Operator
In this eighth part of a nine-part article series focusing on the SELECT statement and its usage in retrieving data from tables, you'll learn how to use the LIKE operator and much more. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).
One final operator you can use in a search condition is the LIKE operator, shown in figure 3-15. You use this operator along with the wildcards shown at the top of this figure to specify the string pattern, or mask, that you want to match. The examples in this figure show how this works.
In the first example, the LIKE phrase specifies that all vendors in cities that start with the letters SAN should be included in the query results. Here, the percent sign (%) indicates that any characters can follow these three letters. So San Diego and Santa Ana are both included in the results.
The second example selects all vendors whose vendor name starts with the letters COMPU, followed by any one character, the letters ER, and any characters after that. Two vendor names that match that pattern are Compuserve and Computerworld.
The LIKE operator provides a powerful technique for finding information in a database that can’t be found using any other technique.
The syntax of the WHERE clause with the LIKE operator
WHERE match_expression [NOT] LIKE pattern
Wildcard symbols
Symbol
Description
%
Matches any string of zero or more characters.
_
Matches any single character.
WHERE clauses that use the LIKE operator
Example
Results that match the mask the mask
WHERE vendor_city LIKE 'SAN%'
"San Diego"and "Santa Ana"
WHERE vendor_name LIKE 'COMPU_ER%'
"Compuserve"and "Computerworld"
Description
You use the LIKE operator to retrieve rows that match a string pattern, called a mask. Within the mask, you can use special characters, called wildcard characters, that determine which values in the column satisfy the condition.
You can use the NOT operator before the LIKE operator. Then, only those rows with values that don’t match the string pattern will be included in the result set.
--------------------------------------------Figure 3-15 How to use the LIKE operator
