Chapter 17

Figure 17-15

Quantifiers

MySQL supports a full range of quantifiers, including the ?, *, and + metacharacters and the {n,m} notation.

Try It Out

Quantifiers in MySQL

For this exercise, use the Parts table.

The ? metacharacter matches an optional preceding character or group. The * metacharacter matches zero or more occurrences of the preceding character or group. The + metacharacter matches one or more occurrences of the preceding character or group.

1.Start the mysql utility, and type the following command to switch to the BRegExp database:

USE BRegExp;

2.Type the following command at the mysql command line:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘^AA?’

;

Figure 17-16 shows the result after Step 2. The pattern ^AA? means A as the first character followed by an optional A. In other words, any part number that begins with A will match.

Figure 17-16

408

Using Regular Expressions with MySQL

3.The pattern ^AA* will match zero or more occurrences of the second A. This will return the same rows as the pattern ^AA?, because that latter pattern is the same as the zero or one matches of ^AA*.

Type the following pattern at the mysql command line to confirm the preceding statement:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘^AA*’

;

4.Inspect the results, and compare with those shown in Figure 17-16. The results are the same. However, if you alter the pattern to ^AA+ you now specify one or more occurrences of A after the initial A. Part numbers that do not have A as the second character will no longer match.

5.Type the following pattern at the mysql command line:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘^AA+’

;

6.Inspect the results, and compare them with the results after Step 4. Figure 17-17 shows the result after Step 5. Now only three rows match. Notice that all rows that previously matched but that have a second character that is not A no longer match. The pattern AA+ means that there must be at least one A after the initial A.

Figure 17-17

The full range of quantifier syntax using the {n,m} syntax is supported in MySQL. The {0,} syntax produces the same results as the * quantifier, which is not surprising, because the meanings are identical. Similarly, the pattern ^AA{1,} returns the same results as the pattern ^AA+. Again, this is not surprising, because A{1,} and A+ mean the same.

7.The {n,m} syntax also works when both n and m are specified. To practice, specify between one and three occurrences of A after the initial A. Type the following at the mysql command line:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘^AA{1,3}’

;

8.Inspect the results returned, and compare them to those from earlier examples.

Three rows are returned. They are the same as those shown in Figure 17-17. This is so because you are matching only a character sequence for which you don’t specify what comes next. Therefore, if four A characters occur after the initial A, the upper limit of three still matches. The existence of a fourth A doesn’t stop three from matching.

409