Matching Character Strings: LIKE

Some problems can't be solved with comparisons. Here are a few examples:

• “His name begins with 'Mc' or 'Mac'—I can't remember the rest.”

• “We need a list of all the 415 area code phone numbers.”

• “I forget the name of the book, but it has a mention of exercise in the notes.”

• “Well, it's Carson, or maybe Karsen—something like that.”

• “His first name is 'Dirk' or 'Dick.' Four letters, starts with a D and ends with a k.”

In each of these cases, you know a pattern embedded somewhere in a column, and you need to use the pattern to retrieve all or part of the row. The LIKE keyword is designed to solve this problem. You can use it with character fields (and on some systems, with date fields). It doesn't work with numeric fields defined as integer, money, and decimal or float. The syntax is this:

WHERE column_name [NOT] LIKE 'pattern'
   [ESCAPE escape_char]

The pattern must be enclosed in quotes and must include one or more wildcards (symbols that take the place of missing letters or strings in the pattern). You use the ESCAPE keyword when your pattern includes one of the wildcards and you need to treat it as a literal.

ANSI SQL provides two wildcard characters for use with LIKE, the percent sign (%) and the underscore or underbar (_).

LIKE Examples

Following are answers to the questions just posed and the queries that generated them. First, the search for Scottish or Irish surnames:

select au_lname, city
from authors
where au_lname like 'Mc%' or au_lname like 'Mac%'
au_lname                               city
====================================== =================
McBadden                               Vacaville
MacFeather                             Oakland
[2 rows]

The LIKE pattern instructs the system to search for a name that begins with “Mc” and is followed by a string of any number of characters (%) or that begins with “Mac” and is followed by any number of characters. Notice that the wildcard is inside the quotes.

Now the 415 area code list:

select au_lname, phone
from authors
where phone like '415%'
au_lname                                 phone
======================================== ============
Bennet                                   415 658-9932
Green                                    415 986-7020
Carson                                   415 548-7723
Stringer                                 415 843-2991
Straight                                 415 834-2919
Karsen                                   415 534-9219
MacFeather                               415 354-7128
Dull                                     415 836-7128
Yokomoto                                 415 935-4228
Hunter                                   415 836-7128
Locksley                                 415 585-4620
(11 rows affected)

Here again, you're looking for some known initial characters followed by a string of unknown characters.

The book with “exercise” somewhere in its notes is a little trickier. You don't know if it's at the beginning or end of the column, and you don't know whether the first letter of the word is capitalized. You can cover all these possibilities by leaving the first letter out of the pattern and using the same “string of zero or more characters” wildcard at the beginning and end of the pattern.

select title_id, notes
from titles
where notes like '%xercise%'
title_id   notes
======== ======================================================
PS2106   New exercise, meditation, and nutritional techniques
         that can reduce the shock of daily interactions.
         Popular audience.  Sample menus included, exercise
         video available separately.
[1 row]

When you know the number of characters missing, you can use the single-character wildcard (_). In the next example, the first letter is either K or C and the next to the last is either e or o. If the authors table contained the last name Karson, it would also be included in the results. Starson or Karstin would not.

select au_lname, city
from authors
where au_lname like '_ars_n'
au_lname                                 city
======================================== ====================
Carson                                   Berkeley
Karsen                                   Oakland
(2 rows affected)

The next example is similar to the previous one. It looks for four-letter first names starting with D and ending with k.

select au_lname, au_fname, city
from authors
where au_fname like 'D_ _k'
au_lname                            au_fname           city
=================================== ================== ==============
Stringer                            Dirk               Oakland
Straight                            Dick               Oakland
[2 rows]

NOT LIKE

You can also use NOT LIKE with wildcards. To find all the phone numbers in the authors table that do not have 415 as the area code, you could use either of these queries (they are equivalent):

select phone
from authors
where phone not like '415%'

select phone
from authors
where not phone like '415%'

Escaping

Wildcard characters are almost always used together with the LIKE keyword. Without LIKE, the wildcard characters are interpreted literally and represent exactly their own values. The query that follows finds any phone numbers that consist of the four characters “415%” only. It will not find phone numbers that start with 415:

select phone
from authors
where phone = '415%'
						

What if you want to search for a value that contains one of the wildcard characters? For example, in one row in the titles table, the notes column contains a claim to increase readers' friends by some percentage. You can search for the percent mark by using ESCAPE to appoint a character to strip the percent sign of its magic meaning and convert it to an ordinary character. A wildcard directly after the escape character has only its literal meaning. Other wildcards continue to have their special significance. In the following LIKE expression, you are looking for a literal percent sign somewhere in the notes column. Since it's probably not the first or last character, you use wildcard percent signs at the beginning and end of the expression and a percent sign preceded by the escape character in the middle.

select title_id, notes
from titles
where notes like '%@%%' escape '@'
title_id  notes
====== =======================================================
TC7777   Detailed instructions on improving your position in
         life by learning how to make authentic Japanese sushi
         in your spare time. 5-10% increase in number of
         friends per recipe reported from beta test.
[1 row]

Following are some examples of LIKE with escaped and unescaped wildcard character searches (the @ sign is the designated escape character):