To identify partial matches, SQL uses two wildcard characters. The percent sign (%) can stand for any string of characters that have zero or more characters. The underscore (_) stands for any single character.
| Statement | Values Returned |
|---|---|
| WHERE Word LIKE ‘intern%’ | intern |
| internal | |
| international | |
| internet | |
| interns | |
| WHERE Word LIKE ‘%Peace%’ | Justice of the Peace |
| Peaceful Warrior | |
| WHERE Word LIKE ‘T_p_’ | Tape |
| Taps | |
| Tipi | |
| Tips | |
| Tops | |
| Type |
WHERE Phone NOT LIKE '503%'This example returns all the rows in the table for which the phone number starts with something other than 503.
You may want to search for a string that includes an actual percent sign or underscore. In that case, you want SQL to interpret the percent sign as a percent sign and not as a wildcard character. You can conduct such a search by typing an escape character just prior to the character you want SQL to take literally.
You can choose any character as the escape character as long as that character doesn’t appear in the string that you’re testing, as shown in the following example:SELECT Quote FROM BARTLETTS WHERE Quote LIKE '20#%' ESCAPE '#' ;The % character is escaped by the preceding # sign, so the statement interprets this symbol as a percent sign rather than as a wildcard. You can “escape” an underscore — or the escape character itself — in the same way. The preceding query, for example, would find the following quotation in Bartlett’s Familiar Quotations:
20% of the salespeople produce 80% of the results.The query would also find the following:
20%