How to use the LIKE operator
One final operator you can use in a search condition is the LIKE operator shown in figure 3-14. You use this operator along with the wildcards shown at the top of this figure to specify a string pattern, or mask, you want to match. The examples shown in this figure illustrate how this works.
In the first example, the LIKE phrase specifies that all vendors in cities that start with the letters SAN should be included in the query results. Here, the percent sign (%) indicates that any characters can follow these three letters. So San Diego and Santa Ana are both included in the results.
The second example selects all vendors whose vendor name starts with the letters COMPU, followed by any one character, the letters ER, and any characters after that. Two vendor names that match that pattern are Compuserve and Computerworld.
The third example searches the values in the VendorContactLName column for a name that can be spelled two different ways: Damien or Damion. To do that, the mask specifies the two possible characters in the fifth position, E and O, within brackets.
The fourth example uses brackets to specify a range of values. In this case, the VendorState column is searched for values that start with the letter N and end with any letter from A to J. That excludes states like Nevada (NV) and New York (NY).
The fifth example shows how to use the caret (^) to exclude one or more characters from the pattern. Here, the pattern says that the value in the VendorState column must start with the letter N, but must not end with the letters K through Y. This produces the same result as the previous statement.
The last example in this figure shows how to use the NOT operator with a LIKE phrase. The condition in this example tests the VendorZipCode column for values that don't start with the numbers 1 through 9. The result is all zip codes that start with the number 0.
The LIKE operator provides a powerful technique for finding information in a database that can't be found using any other technique. Keep in mind, however, that this technique requires a lot of overhead, so it can reduce system performance. For this reason, you should avoid using the LIKE operator in production SQL code whenever possible.
If you need to search the text that's stored in your database, a better option is to use the Integrated Full-Text Search (iFTS) feature that's provided by SQL Server 2008. This feature provides more powerful and flexible ways to search for text, and it performs more efficiently than the LIKE operator. However, iFTS is an advanced feature that requires some setup and administration and is too complex to explain here. For more information, you can look up "full-text search" in Books Online.
The syntax of the WHERE clause with a LIKE phrase
WHERE match_expression [NOT] LIKE pattern
Wildcard symbols
WHERE clauses that use the LIKE operator
Description
Figure 3-14 How to use the LIKE operator