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

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

{mospagebreak title=How to use the IS NULL condition}

In chapter 1, you learned that a column can contain a null value. A null isn’t the same as zero, a blank string that contains one or more spaces (‘ ’), or an empty string that doesn’t contain any spaces (‘’). Instead, a null value indicates that the data is not applicable, not available, or unknown. When you allow null values in one or more columns, you need to know how to test for them in search conditions. To do that, you can use the IS NULL condition, as shown in figure 3-16.

This figure uses a table named null_sample to illustrate how to search for null values. This table contains two columns. The first column, invoice_id, is an identification column. The second column, invoice_total, contains the total for the invoice, which can be a null value. As you can see in the first example, the invoice with an invoice_id of 3 contains a null value.

The second example in this figure shows what happens when you retrieve all the invoices with invoice totals equal to zero. Notice that the row with a null invoice total isn’t included in the result set. Likewise, it isn’t included in the result set that contains all the invoices with invoices totals that aren’t equal to zero, as illustrated by the third example. Instead, you have to use the IS NULL condition to retrieve rows with null values, as shown in the fourth example.

You can also use the NOT operator with the IS NULL condition as illustrated in the last example in this figure. When you use this operator, all of the rows that don’t contain null values are included in the query results.

The syntax of the WHERE clause with the IS NULL condition

WHERE expression IS [NOT] NULL

The contents of the Null_Sample table

SELECT *
FROM null_sample

A SELECT statement that retrieves rows with zero values

SELECT *
FROM null_sample
WHERE invoice_total = 0

A SELECT statement that retrieves rows with non-zero values

SELECT *
FROM null_sample
WHERE invoice_total <> 0

A SELECT statement that retrieves rows with null values

SELECT *
FROM null_sample
WHERE invoice_total IS NULL

A SELECT statement that retrieves rows without null values

SELECT *
FROM null_sample
WHERE invoice_total IS NOT NULL

——————————————–Figure 3-16 How to use the IS NULL condition

{mospagebreak title=How to code the ORDER BY clause}

The ORDER BY clause specifies the sort order for the rows in a result set. In most cases, you can use column names from the base table to specify the sort order as you saw in some of the examples earlier in this chapter. However, you can also use other techniques to sort the rows in a result set, as described in the topics that follow.

How to sort a result set by a column name

Figure 3-17 presents the expanded syntax of the ORDER BY clause. As you can see, you can sort by one or more expressions in either ascending or descending sequence. This is illustrated by the three examples in this figure.

The first two examples show how to sort the rows in a result set by a single column. In the first example, the rows in the vendors table are sorted in ascending sequence by the vendor_name column. Since ascending is the default sequence, the ASC keyword is omitted. In the second example, the rows are sorted by the vendor_name column in descending sequence.

To sort by more then one column, you simply list the names in the ORDER BY clause separated by commas as shown in the third example. Here, the rows in the Vendors table are first sorted by the vendor_state column in ascending sequence. Then, within each state, the rows are sorted by the vendor_city column in ascending sequence. Finally, within each city, the rows are sorted by the vendor_name column in ascending sequence. This can be referred to as a nested sort because one sort is nested within another.

Although all of the columns in this example are sorted in ascending sequence, you should know that doesn’t have to be the case. For example, we could have sorted by the vendor_name column in descending sequence like this:

ORDER BY vendor_state, vendor_city, vendor_name DESC

Note that the DESC keyword in this example applies only to the vendor_name column. The vendor_state and vendor_city columns are still sorted in ascending sequence.

If you study the first example in this figure, you can see that capital letters come before lowercase letters in an ascending sort. As a result, “ASC Signs” comes before “Abbey Office Furnishings” in the result set. For some business applications, this is acceptable. But if it isn’t, you can use the LOWER function to convert the column to lowercase letters in the ORDER BY clause like this:

ORDER BY LOWER(vendor_name)

Then, the rows will be sorted in the correct alphabetical sequence. In chapter 8, you can learn more about this function.

The expanded syntax of the ORDER BY clause

ORDER BY expression [ASC|DESC] [, expression [ASC|DESC]] …

An ORDER BY clause that sorts by one column in ascending sequence

SELECT vendor_name,
vendor_city || ‘, ‘ || vendor_state || ‘ ‘ || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_name

An ORDER BY clause that sorts by one column in descending sequence

SELECT vendor_name,
vendor_city || ‘, ‘ || vendor_state || ‘ ‘ || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_name DESC

An ORDER BY clause that sorts by three columns

SELECT vendor_name,
vendor_city || ‘, ‘ || vendor_state || ‘ ‘ || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_state, vendor_city, vendor_name

Description

  1. The ORDER BY clause specifies how you want the rows in the result set sorted. You can sort by one or more columns, and you can sort each column in either ascending (ASC) or descending (DESC) sequence. ASC is the default.
  2. By default, in an ascending sort, special characters appear first in the sort sequence, followed by numbers, then by capital letters, then by lowercase letters, and then by null values. In a descending sort, this sequence is reversed.
  3. With one exception, you can sort by any column in the base table, regardless of whether it’s included in the SELECT clause. The exception is if the query includes the DISTINCT keyword. Then, you can only sort by columns included in the SELECT clause.

——————————————–Figure 3-17 How to sort a result set by a column name

Please check back for the conclusion to this series.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan