Home arrow Oracle arrow 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).

TABLE OF CONTENTS:
  1. Retrieving Table Data with the LIKE Operator
  2. How to use the IS NULL condition
  3. How to code the ORDER BY clause
By: Murach Publishing
Rating: starstarstarstarstar / 0
August 31, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

How to use the LIKE operator

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

  1. 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.
  2. 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



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

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