Regular Expressions and Microsoft Access

Figure 18-9

13.Close the query.

14.From the database objects window, double-click Hill in ItemTitle. The results shown in Figure 18-10 should be displayed.

Figure 18-10

Each of the three rows of data displayed contains the character sequence hill. In the sample data, each of the occurrences of hill is part of the character sequences Churchill (twice) or Churchill’s. If you create a similar query but specify the LIKE “*Hill*” criterion for the ItemAuthor column, you will see two rows returned with the character sequence Hill as the word Hill and Hills. The query Hill in ItemAuthor is provided in the sample database to allow you to test this.

Creating a Parameter Query

In some settings a hard-wired query as specified in the preceding section can be useful, particularly if it has a little more to it than the simple example just described. For example, a query that retrieves sales data for a particular period is likely to be run many times.

419

Chapter 18

However, rather than hard-wiring the literal part of the regular expression pattern, you can achieve much more flexibility if you allow the user to specify the character sequence that is being searched for. Most of the steps required to create a parameter query are similar to those needed to create a hard-wired query, as described in the preceding section. The similar parts of the following example will, therefore, be described fairly sparsely. Refer to the preceding section, as necessary, for further detail and figures to illustrate what you can expect to see while creating the query.

Try It Out

Creating a Parameter Query

1.Open Access, highlight Queries in the left pane of the database objects window, and click the New button.

2.In the New Query window, select Design View, and click OK

3.Click on the dBeachPurchases table to select it, and then click the Add button.

4.Click the Close button.

5.In the leftmost column, select ItemTitle in the Field row, and in the Criteria row, type the following code:

LIKE “*” & [Enter a character sequence to search for:] & “*”

Be careful not to enclose the middle part inside paired quotes. In other words, don’t put quotation marks outside the square brackets. If you insert quotation marks there, you won’t create a parameter query.

6.In the next column, select ItemAuthor in the Field row.

7.Save the query, naming the query Find a sequence in ItemTitle, and close the query.

8.From the database objects window, select Queries in the left pane, and double-click the Find a sequence in ItemTitle query.

The Enter Parameter Value dialog box should open and should look like the one shown in Figure 18-11.

Figure 18-11

420

Regular Expressions and Microsoft Access

9.Type Love in the text box in the Enter Parameter Value window. Figure 18-12 shows the appearance after this step. The titles returned include the word Love and Lovers.

Figure 18-12

10.Double-click the Find a sequence in ItemTitle query, and in the Enter Parameter Value dialog box, enter the character sequence Men. Figure 18-13 shows the results.

Figure 18-13

Notice in the figure that you match two different rows, each of which refers to the title Of Mice and Men, but which record that title in two different ways. The pattern *Men*, which is the pattern created behind the scenes, will match the character sequence Men wherever it occurs in an ItemTitle field, including as part of the word women in one of the displayed results.

421

Chapter 18

The Metacharacters Suppor ted in Access

Access has a fairly limited range of regular expression support compared to a database management system like MySQL. However, it does provide useful filtering functionality when carrying out queries.

The following table summarizes the metacharacters supported in Access 2003.

If you are accessing data in Access using ADO, a different set of metacharacters, not covered in detail in this chapter, is available. You use % instead of * and _ instead of ?.

Metacharacter	Description
?	Matches a single character
*	Matches zero or more characters
#	Matches any single numeric digit
[ ...]	Matches any single character among those listed
	inside the square brackets
[! ...]	Matches any single character not among those listed
	inside the square brackets

The examples that are described and worked through in the following sections all use the

AuctionPurchases.mdb database.

Using the ? Metacharacter

The ? metacharacter matches a single character.

The following example creates a query that displays rows where the author-name contains H followed by any character followed by the character sequence ll. The pattern to be used in the query is H?ll.

Try It Out

Using the ? Metacharacter

1.Open Access, highlight Queries in the left pane of the database objects window, and click the New button.

2.In the New Query window, select Design View, and click OK

3.Click the dBeachPurchases table to select it, and then click the Add button.

4.Click the Close button.

5.In the leftmost column, select ItemTitle in the Field row.

6.In the next column, select ItemAuthor in the Field row.

7.In the Criteria row, type the following code:

Like “*H?ll*”

422