Chapter 17

Figure 17-10

Using Positional Metacharacters

MySQL supports both the beginning-of-field (column) metacharacter, ^, and the end-of-field metacharacter, $. If these positional metacharacters are not included in a pattern, the pattern will match if a relevant character sequence occurs anywhere in the field specified in the WHERE clause.

For the following exercise, use the data in the Employees table.

Try It Out

Positional Metacharacters

First, you will run code with no positional metacharacters.

1.At the mysql command prompt, type the following command:

USE BRegExp;

2.Then type the following command:

SELECT ID, LastName, FirstName, Skills

FROM Employees

WHERE Skills REGEXP ‘Data’

;

Rows will be displayed where the Skills column includes the character sequence Data. Figure 17-11 shows the result after Step 2. Two rows are displayed: the data for George Smith and Dai LLareggub.

404

Using Regular Expressions with MySQL

Figure 17-11

Notice that only the data for Dai Llareggub has the character sequence Data at the beginning of the field. So if you add the positional metacharacter ^ to the pattern, only that row should be displayed.

3.Type the following SQL code at the mysql command line:

SELECT ID, LastName, FirstName, Skills FROM Employees

WHERE Skills REGEXP ‘^Data’

;

Notice in Figure 17-12 that only one row of data, the data for Dai Llareggub, is displayed. The data for George Smith is no longer displayed, because the beginning-of-field position specified by the ^ metacharacter is not followed by the character sequence Data.

Figure 17-12

4.Next, you will demonstrate that the RLIKE keyword works in the same way as the REGEXP keyword. Type the following command at the mysql command line:

SELECT ID, LastName, FirstName, Skills FROM Employees

WHERE Skills RLIKE ‘^Data’

;

The result from Step 4 is also displayed in Figure 17-12.

405

Chapter 17

5.The $ metacharacter works as the end-of-field (column) metacharacter. You will first attempt to match the character sequence Tra without the $ metacharacter. So you are attempting to match all rows that contain the character sequence Tra anywhere in the Skills column.

Type the following command at the mysql command prompt:

SELECT ID, LastName, FirstName, Skills FROM Employees

WHERE Skills REGEXP ‘Tra’

;

Two rows are displayed, those for George Smith and Dai LLareggub. When you include the $ metacharacter in the pattern, only one row should be displayed: the row of data for George Smith.

6.Type the following command at the mysql command prompt:

SELECT ID, LastName, FirstName, Skills FROM Employees

WHERE Skills REGEXP ‘Tra$’

;

Because only the data for George Smith has the character sequence Tra as its final characters, only that row is displayed, as shown in Figure 17-13.

Figure 17-13

Using Character Classes

MySQL supports character classes and negated character classes. The syntax is standard. The pattern [ABCDE] specifies a character class containing the uppercase alphabetic characters A, B, C, D, and E. The character class [A-E] has the same meaning but is expressed using a character class range.

Try It Out

Using Character Classes

This example uses the Parts table.

1.Type the following command at the mysql command prompt:

USE BRegExp;

406

Using Regular Expressions with MySQL

2.Type the following command to select parts that begin with A, then B, C, or D:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘A[ABCD]’

;

Figure 17-14 shows the appearance after Step 2. Eight rows are returned in the results. You will see that each part number begins with the uppercase A and is followed by A, B, C, or D.

Figure 17-14

3.Character ranges are supported in MySQL. So try using a character class with the range pattern [A-D] to return the same results as in Step 2.

Type the following command at the mysql command prompt:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘A[A-D]’

;

The results from the preceding code are the same as those shown in Figure 17-14.

4.Negated character classes are also supported. So you can negate the preceding character class to return rows that begin with A and that have a second character that does not match the pattern

[A-D].

Type the following SQL code at the mysql command prompt:

SELECT ID, PartNum, Description FROM Parts

WHERE PartNum REGEXP ‘A[^A-D]’

;

5.Inspect the results, comparing them to the results returned from the preceding character classes. Figure 17-15 shows the appearance after the preceding code has been run. Notice that the rows returned were not returned by code that contained the positive character classes [ABCD] or [A-D].

407