Chapter 16

When the pattern R% is used in relation to the au_lname column and the pattern Al% is used in relation to the au_fname column, any row where the au_lname column begins with R is matched. From those matches, any row where the au_fname column begins with Al is matched. Only rows where both attempts at matching succeed are displayed.

When the NOT keyword is used together with the LIKE keyword and a pattern as, for example, in the following WHERE clause, only rows where the last name fails to match the value of the pattern are matched:

WHERE au_lname NOT LIKE ‘B%’

In this case, the pattern matches last names that begin with the character B. So only last names beginning with a character other than B are matched.

The _ Metacharacter

The _ metacharacter matches exactly one character. It is similar to the dot metacharacter in more standard regular expression syntax.

Try It Out

Using the _ Metacharacter

1.Open the Query Analyzer. Type the following Transact-SQL code into the query pane of the Query Analyzer:

USE Northwind

SELECT SupplierID, ProductID, ProductName FROM dbo.products

WHERE SupplierID LIKE ‘1%’

ORDER BY SupplierID

Notice that you are now using the Northwind sample database, as specified by the first line of the Transact-SQL code. Using the pattern 1% in the third line will show you that there are some SupplierID values consisting only of the character 1.

2.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-6 shows the appearance after this step. Notice that the first Supplier ID values displayed consist of the character 1 only. When you use the _ metacharacter later in this Try It Out, those rows should not be displayed.

3.Type the following code into the query pane of Query Analyzer:

USE Northwind

SELECT SupplierID, ProductID, ProductName FROM dbo.products

WHERE SupplierID LIKE ‘1_’

ORDER BY SupplierID

You can simply replace the pattern 1% with the pattern 1_ in the third line of the Transact-SQL code.

372

Regular Expression Functionality in SQL Server 2000

Figure 16-6

4.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-7 shows the appearance after this step. Notice that the first three rows that were displayed in Figure 16-6 are no longer displayed.

How It Works

The pattern 1% matches SupplierID values that begin with the numeric digit 1 followed by zero or more characters. So it matches values such as 1 and 10.

The pattern 1_ matches SupplierID values only if they begin with the numeric digit 1 followed by exactly one character. Thus, the value 1 does not match, because 1 is followed by zero characters. However, values such as 10 do match, because the 1 is followed in the value by exactly one character.

Character Classes

Support for character classes in SQL Server 2000 provides the behavior that you are familiar with in other implementations. Character class ranges are supported.

373

Chapter 16

Figure 16-7

Character classes provide useful functionality that complements usage of the % and _ metacharacters. For example, you can use the character class [ABC] in the pattern [ABC]% to match authors whose last name begins with A or B or C.

Try It Out

Character Classes

1.Open Query Analyzer, and type the following code in the query pane of Query Analyzer:

USE pubs

SELECT au_lname, au_fname from dbo.authors

WHERE au_lname LIKE ‘[ABC]%’

ORDER BY au_lname

2.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-8 shows the appearance after this step. Because no author last names begin with A, only those last names beginning with B or C are displayed.

374

Regular Expression Functionality in SQL Server 2000

Figure 16-8

3.Character class ranges can also be used. For example, to display rows containing last names that begin with characters in the range N through Z, you can use the character class [N-Z] in the pattern [N-Z]%.

Type the following code in the query pane of the Query Analyzer:

USE pubs

SELECT au_lname, au_fname from dbo.authors

WHERE au_lname LIKE ‘[N-Z]%’

ORDER BY au_lname

4.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-9 shows the appearance after this step.

How It Works

The character class [ABC] matches a single character that is A or B or C. The pattern [ABC]% is thus equivalent to the combination of the patterns A%, B%, and C%. The pattern A% matches authors whose last name begins with A. The patterns B% and C% match last names beginning with B and C, respectively. Putting all three together, the pattern [ABC]% matches last names beginning with A, B, or C.

375