How To Receive Data from a Single Table, concluded - How to use the LIKE operator
(Page 2 of 5 )
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.
Figure 3-14. How to use the LIKE operator
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
- 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.
Next: How to use the IS NULL clause >>
More MS SQL Server Articles
More By Murach Publishing
|
 This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 11 
