Microsoft® SQL Server® 2012 Bible

125

Chapter 6: Introducing Basic Query Flow

6

AL

AR

AS

AS

...

It’s diffi cult to prove a negative, especially when a null value is involved. Because the

meaning of null is “unknown,” the value being searched for could be in the list. The fol-

lowing code sample demonstrates how a null in the list makes it impossible to prove that

'A' is not in the list:

SELECT 'IN' WHERE 'A' NOT IN ('B',NULL);

There’s no result because the unknown null value might simply be an “A.” Because SQL

can’t logically prove that “A” is not in the list, the WHERE clause returns a false. Anytime

a NOT IN condition is mixed with a null in the list, every row will be evaluated as false.

Using the LIKE Search Condition

The LIKE search condition uses wildcards to search for patterns within a string. The wild-

cards, however, are different from the MS-DOS wildcards with which you may be familiar.

Table 6-2 shows both the SQL and MS-DOS wildcards.

TABLE 6 -2 SQL Wildcards

Description SQL Wildcard MS-DOS Wildcard Example

Any number (zero or more) of

(^) arbitrary characters
% * ‘A b l e’ L IK E ‘A%’
One arbitrary character ? ‘Able’ LIKE ‘Abl’
One of the enclosed characters [ ] n/a ‘a’ LIKE ‘[a-g]’
‘a’ LIKE ‘[abcdefg]’
Match not in range of characters ['] n/a ‘a’ LIKE ‘['w-z]’
‘a’ LIKE ‘['wxyz] ‘
The next query uses the LIKE search condition to locate all products that begin with
'Chain' optionally followed by any number of characters:
USE AdventureWorks;
SELECT Name
FROM Production.Product
WHERE
Name LIKE 'Chain%'
c06.indd 125c06.indd 125 7/30/2012 4:16:03 PM7/30/2012 4:16:03 PM
http://www.it-ebooks.info