Chapter 15

Using Wildcards in Data Forms

Tabular data in Excel can be held in a list. The sample file, Names.xls, contains a simple list that contains last name, first name, and date of birth for several people.

Try It Out

Using Wildcards in Data Forms

1.Open Names.xls in Excel.

2.From the Data menu, select Form. Inspect the display of the list data. The data form, titled Sheet 1, opens. Notice that there is a slider in the middle of the data form.

3.Click the Criteria button. Figure 15-12 shows the appearance after this step. The Criteria button is replaced by the Form button. You can now enter criteria to match data in the data form.

Figure 15-12

4.To match last names that begin with the character sequence St you can use the pattern St* in the LastName text box in the data form. So type the pattern St* in the LastName text box; click the Find Next button; and inspect the results, as shown in Figure 15-13. Notice that the

slider in the center of the data form is now close to the bottom. This is because the first match is the eighth of the ten rows. Notice, too, that when data is displayed the Criteria button reappears.

5.Click the Find Next button again, and confirm that the ninth row is matched. The Find Previous button allows navigation back to the eighth row.

6.Wildcards can be used in a combination of text boxes. You can, for example, search for people whose last name contains St and whose first name contains kar using the following steps:

a.Click the Criteria button.

b.In the LastName text box, enter the pattern St*.

360

Wildcards in Microsoft Excel

c.In the FirstName text box enter the pattern Kar?.

d.Click the Find Next button, and inspect the appearance of the data form. Figure 15-14 shows the appearance after this step. In this limited data set there is only one match, Kara Stelman.

Figure 15-13

Figure 15-14

361

Chapter 15

How It Works

With the pattern St* in the LastName text box, surnames that contain the character sequence St or st are matched.

With the pattern St* in the LastName text box and Kar? in the first name text box, only names that contain the character sequence Kar followed by one character in the first name and that contain the character sequence St followed by any number of characters in the last name are matched.

Using Wildcards in Filters

Excel contains powerful filter functionality that can be applied to tabular data that constitutes a list.

Try It Out

Using Wildcards in Excel Filters

1.Open Names.xls in Excel. From the Data menu select Filter, and then select AutoFilter. You should see something similar to that shown in Figure 15-15. The file NamesWithFilter.xls is made available should you have difficulty creating the AutoFilter.

Notice the drop-down lists contained inside cells B3, C3, and D3. They give access to the AutoFilter functionality.

Figure 15-15

2.Click the drop-down list in the cell with the value LastName (cell B3), and click the Custom option in the menu offered.

After this step, the appearance should be as in Figure 15-16. The Custom AutoFilter dialog box allows selected rows to be displayed.

3.In the top-left drop-down list, select the Begins With option (you will have to scroll down to select it).

4.In the top-right text box, enter the pattern St*; click the OK button; and inspect the results, as shown in Figure 15-17. Notice that now only two of the ten rows are displayed. The displayed rows are those where the last name begins with the character sequence St.

362

Wildcards in Microsoft Excel

Figure 15-16

Figure 15-17

The Custom AutoFilter offers advantages over the data form in some respects. For example, it allows the user to specify that a pattern occurs at the beginning or end of a cell’s value. This gives functionality similar to the ^ and $ metacharacters. In addition, AND or OR logic can be used to create two rules.

However, the Custom AutoFilter allows patterns to be defined for only one column. The data form criteria can be defined on any combination of the columns in the list.

Exercises

1.How can you display in NamesWithFilter.xls only names where the first name begins with

Kar?

2.In Months.xls, describe how you would match only cells that contain the character sequence Jun or the character sequence Jul.

363

16

Regular Expression

Functionality in

SQL Ser ver 2000

Vast quantities of business and other data are stored in SQL Server, Microsoft’s premier relational database product. Most administrators and developers who use SQL Server are familiar with the problems that can occur when retrieving data from a very large data store. The better a developer or user understands the data, the better the retrieval of data is achieved. The sensitivity and specificity of the retrieval of desired data from SQL Server can be improved by using regular expression functionality.

Regular expression–like functionality can be achieved using the LIKE keyword in a WHERE clause or by using SQL Server’s full-text indexing and search capability.

This chapter describes functionality in SQL Server 2000. Similar functionality is present in the beta of SQL Server 2005.

In this chapter, you will learn the following:

Which metacharacters are supported in SQL Server 2000

How to use the supported metacharacters with the LIKE keyword

How to achieve regular expression–like functionality using full-text search

The examples in this chapter assume that you have SQL Server 2000 installed as a local, unnamed instance. If you are running SQL Server 2000 as a named instance, modify the connection information accordingly, if necessary.