Chapter 16

Figure 16-9

The character class [N-Z] is equivalent to the character class [NOPQRSTUVWXYZ]. Thus, last names that begin with any character from N through Z are matched by the pattern [N-Z]%.

Negated Character Classes

Negated character classes are supported in SQL Server 2000. The ^ metacharacter is used immediately following the initial square bracket to signify that a negated character class is being specified.

Negated character classes can be used together with ranges in a character class.

Try It Out

Negated Character Classes

1.Open Query Analyzer, and in the query pane, type the following Transact-SQL code:

USE AdventureWorks2000

SELECT LastName, FirstName from dbo.contact

WHERE LastName LIKE ‘Ad%’

ORDER BY LastName, FirstName

376

Regular Expression Functionality in SQL Server 2000

This example uses the AdventureWorks2000 database. Be careful about the capitalization of the database name AdventureWorks2000. Also ensure that you don’t insert a space into the database name. The preceding code matches last names that begin with Ad. When you use a negated character class in a moment, you will see that those rows are not matched.

2.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-10 shows the appearance after this step. Notice that there are, for example, four rows where the surname Adams is displayed.

3.Type the following code into the query pane:

USE AdventureWorks2000

SELECT LastName, FirstName from dbo.contact

WHERE LastName LIKE ‘A[^d]%’

ORDER BY LastName, FirstName

4.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-11 shows the appearance after this step. Notice that the rows containing the last name Adams are not displayed. They would be displayed between rows 6 and 7 if they had been matched, given the ORDER BY clause.

Figure 16-10

377

Chapter 16

Figure 16-11

5.Negated character classes can be used with ranges. For example, if you wanted to match last names that begin with A where the next character is not in the range a through k, you can use the pattern A[^a-k]%.

Type the following code into the query pane:

USE AdventureWorks2000

SELECT LastName, FirstName from dbo.contact

WHERE LastName LIKE ‘A[^a-k]%’

ORDER BY LastName, FirstName

6.Press F5 or click the blue right-pointing arrow above the query pane to run the Transact-SQL code. Figure 16-12 shows the appearance after this step. Notice that no last name that begins with A and has its second character in the range a through k is displayed in the results pane.

378