How to Retrieve Data from a Single Table - How to Use the LIKE Operator
(Page 14 of 17 )
One final operator you can use in a search condition is the LIKE operator shown in figure 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.
Figure 14: The syntax of the WHERE clause with a LIKE phrase
WHERE match_expression [NOT] LIKE pattern
Wildcard symbols
| Symbol | Description |
| % | Matches any string of zero or more characters. |
| _ | Matches any single character. |
| [ ] | Matches a single character within the brackets. |
| [ - ] | Matches a single character within the given range. |
| [ ^ ] | Matches a single character not listed after the caret. |
WHERE clauses that use the LIKE operator:
| Example | Results that match the mask |
| WHERE VendorCity LIKE 'SAN%' | "San Diego" and Santa Ana" |
| WHERE VendorName LIKE 'COMPU_ER%' | "Compuserve" and "Computerworld" |
| WHERE VendorContactLName LIKE 'DAMI[EO]N' | "Damien" and Damion" |
| WHERE VendorState LIKE 'N[A-J]' | "NC" and "NJ" but not "NV" or "NY" |
| WHERE VendorState LIKE 'N[^K-Y]' | "NC" and "NJ" but not "NV" or "NY" |
| WHERE VendorZipCode NOT LIKE '[1-9]%' | "02107" and "08816" |
Description
- You use the LIKE operator to retrieve rows that match a string pattern, called a mask. Within the mask, you can use special characters, called wildcards, that determine which values in the column satisfy the condition.
- You can use the NOT keyword before the LIKE keyword. Then, only those rows with values that don't match the string pattern will be included in the result set.
- Most LIKE phrases will significantly degrade performance compared to other types of searches, so use them only when necessary.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Use the IS NULL Clause >>
More MS SQL Server Articles
More By Bryan Syverson
