How to Use LIKE and NOT LIKE Predicates in SQL Statements

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.

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:

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%