You can use the LIKE SQL predicate to compare two character strings for a partial match. Partial matches are valuable if you don’t know the exact form of the string for which you’re searching. You can also use partial matches to retrieve multiple rows that contain similar strings in one of the table’s columns.

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
WHERE Word LIKE ‘%Peace%’ Justice of the Peace
Peaceful Warrior
WHERE Word LIKE ‘T_p_’ Tape
The NOT LIKE predicate retrieves all rows that don’t satisfy a partial match, including one or more wildcard characters, as in the following example:
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:
 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:

About This Article

This article is from the book:

About the book author:

Allen G. Taylor is a 30-year veteran of the computer industry and the author of over 40 books, including SQL For Dummies and Crystal Reports For Dummies. He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider.

This article can be found in the category: