SQL Visual QuickStart Guide: Retrieving Data from a Table

Jan 5, 2009

📄 Contents

␡

⎙ Print

< Back Page 7 of 10 Next >

This chapter is from the book 

SQL: Visual QuickStart Guide, 3rd Edition

Learn More Buy

Matching Patterns with `LIKE`

The preceding examples retrieved rows based on the exact value of a column or columns. You can use LIKE to retrieve rows based on partial information. LIKE is useful if you don’t know an exact value (“The author’s last name is Kel-something”) or you want to retrieve rows with similar values (“Which authors live in the San Francisco Bay Area?”). The LIKE condition’s important characteristics are:

LIKE works with only character strings, not numbers or datetimes.

LIKE uses a pattern that values are matched against. A pattern is a quoted string that contains the literal characters to match and any combination of wildcards. Wildcards are special characters used to match parts of a value. Table 4.7 lists the wildcard operators, and Table 4.8 lists some example patterns.

Table 4.7. Wildcard Operators

OPERATOR	MATCHES
`%`	A percent sign matches any string of zero or more characters.
`_`	An underscore matches any one character.

Table 4.8. Examples of % and _ Patterns

PATTERN	MATCHES
`'A%'`	Matches a string of length `≥` 1 that begins with A, including the single letter A. Matches `'A'`, `'Anonymous'`, and `'AC/DC'`.
`'%s'`	Matches a string of length `≥` 1 that ends with s, including the single letter s. A string with trailing spaces (after the s) won’t match. Matches `'s'`, `'Victoria Falls'`, and `'DBMSs'`.
`'%in%'`	Matches a string of length `≥` 2 that contains in anywhere. Matches `'in'`, `'inch'`, `'Pine'`, `'linchpin'`, and `'lynchpin'`.
`'____'`	Matches any four-character string. Matches `'ABCD'`, `'I am'`, and `'Jack'`.
`'Qua__'`	Matches any five-character string that begins with Qua. Matches `'Quack'`, `'Quaff'`, and `'Quake'`.
`'_re_'`	Matches any four-character string that has re as its second and third characters. Matches `'Tree'`, `'area'`, and `'fret'`.
`'_re%'`	Matches a string of length `≥` 3 that begins with any character and has re as its second and third characters. Matches `'Tree'`, `'area'`, `'fret'`, `'are'`, and `'fretful'`.
`'%re_'`	Matches a string of length `≥` 3 that has re as the second and third characters from its end and ends with any character. Matches `'Tree'`, `'area'`, `'fret'`, `'red'`, and `'Blood red'`.

String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS Tip in “Filtering Rows with WHERE” earlier in this chapter.
You can negate a LIKE condition with NOT LIKE.
You can combine LIKE conditions and other conditions with AND and OR.

To filter rows by matching a pattern:

Type:
```
SELECT columns
  FROM table
  WHERE test_column [NOT] LIKE
        'pattern';
```
columns is one or more comma-separated column names, and table is the name of the table that contains columns.

In the search condition, test_column is the name of a column in table (test_column doesn’t have to be listed in columns), and pattern is the pattern that’s compared with the value in test_column. pattern is a string like one of the examples listed in Table 4.8. Specify NOT LIKE to retrieve rows with values that don’t match pattern (Listings 4.30 through 4.33, Figures 4.30 through 4.33).

Listing 4.30. List the authors whose last names begin with Kel. See Figure 4.30 for the result.
```
SELECT au_fname, au_lname
  FROM authors
  WHERE au_lname LIKE 'Kel%';
```
Figure 4.30 Result of Listing 4.30.
```
au_fname  au_lname
--------- --------
Christian Kells
          Kellsey
```
Listing 4.31. List the authors whose last names have ll (el-el) as the third and fourth characters. See Figure 4.31 for the result.
```
SELECT au_fname, au_lname
  FROM authors
  WHERE au_lname LIKE '__ll%';
```
Figure 4.31 Result of Listing 4.31.
```
au_fname  au_lname
--------- --------
Hallie    Hull
Klee      Hull
Christian Kells
          Kellsey
```
Listing 4.32. List the authors who live in the San Francisco Bay Area. (Zip codes in that area begin with 94.) See Figure 4.32 for the result.
```
SELECT au_fname, au_lname, city, state, zip
  FROM authors
  WHERE zip LIKE '94___';
```
Figure 4.32 Result of Listing 4.32.
```
au_fname au_lname city          state zip
-------- -------- ------------- ----- -----
Hallie   Hull     San Francisco CA    94123
Klee     Hull     San Francisco CA    94123
         Kellsey  Palo Alto     CA    94305
```
Listing 4.33. List the authors who live outside the 212, 415, and 303 area codes. This example shows three alternative patterns for excluding telephone numbers. You should favor the first alternative because single-character matches (_) are faster than multiple-character ones (%). See Figure 4.33 for the result.
```
SELECT au_fname, au_lname, phone
  FROM authors
  WHERE phone NOT LIKE '212-___-____'
    AND phone NOT LIKE '415-___-%'
    AND phone NOT LIKE '303-%';
```
Figure 4.33 Result of Listing 4.33.
```
au_fname au_lname    phone
-------- ----------- ------------
Sarah    Buchman     718-496-7223
         Kellsey     650-836-7128
Paddy    O'Furniture 941-925-0752
```

You can search for values that contain the special wildcard characters. Use the ESCAPE keyword to specify an escape character that you can use to search for a percent sign or underscore as a literal character. Immediately precede a wildcard character with an escape character to strip the wildcard of its special meaning. If the escape character is !, for example, !% in a pattern searches values for a literal %. (Unescaped wildcards still have their special meaning.) The escape character can’t be part of the value that you’re trying to retrieve; if you’re searching for '50% OFF!', choose an escape character other than !. Table 4.9 shows some examples of escaped and unescaped patterns; the designated escape character is !.

Table 4.9. Escaped and Unescaped Patterns

PATTERN	MATCHES
`'100%'`	Unescaped. Matches 100 followed by a string of zero or more characters.
`'100!%'`	Escaped. Matches `'100%'`.
`'_op'`	Unescaped. Matches `'top'`, `'hop'`, `'pop'`, and so on.
`'!_op'`	Escaped. Matches `'_op'`.

To match a wildcard character:

Type:
```
SELECT columns
  FROM table
  WHERE test_column [NOT] LIKE
        'pattern'
        ESCAPE 'escape_char';
```
The syntax is the same as the SELECT statement in “To filter rows by matching a pattern,” earlier in this chapter, except for the ESCAPE clause. escape_char is a single character. Any character in pattern that follows escape_char is interpreted literally; escape_char itself is not considered to be part of the search pattern (Listing 4.34 and Figure 4.34).

Listing 4.34. List the titles that contain percent signs. Only the % that follows the escape character ! has its literal meaning; the other two percent signs still act as wildcards. See Figure 4.34 for the result.
```
SELECT title_name
  FROM titles
  WHERE title_name LIKE '%!%%' ESCAPE '!';
```
Figure 4.34 Result of Listing 4.34. An empty result. No title names contain a % character.
```
title_name

----------------------------------------
```

Tips

test_column can be an expression.
The NOT that can precede LIKE is independent of the NOT that can precede test_column (see “The NOT operator” earlier in this chapter). This clause
```
WHERE phone NOT LIKE '212-%'
```
is equivalent to this one:
```
WHERE NOT phone LIKE '212-%'
```
You even can write this silly double negation, which retrieves everyone with a 212 area code:
```
WHERE NOT phone NOT LIKE '212-%'
```
Wildcard searches are time-consuming—particularly if you use % at the start of a pattern. Don’t use wildcards if another type of search will do.
In the simplest case in which a pattern contains no wildcards, LIKE works like an = comparison (and NOT LIKE works like <>). In many cases
```
WHERE city LIKE 'New York'
```
is equivalent to
```
WHERE city = 'New York'
```
But these comparisons will differ if your DBMS takes trailing spaces into account for LIKE but not for =. If that’s not important, the = form usually is faster than LIKE.

Microsoft Access doesn’t support the ESCAPE clause. Instead, surround a wildcard character with brackets to render it a literal character. To run Listing 4.34, replace the WHERE clause with:

WHERE title_name LIKE '%[%]%'

Some DBMSs let you use regular expressions to match patterns. Microsoft SQL Server, for example, supports a limited variant of POSIX-style regular expressions. The [] wildcard matches any single character within a range or set, and the [^] wildcard matches any single character not within a range or set. Table 4.10 lists some examples. The SQL standard uses the SIMILAR operator for regex matching. Regex support varies by DBMS; search your DBMS documentation for LIKE, regular expressions, or pattern matching.

Table 4.10. Examples of [] and [^] Patterns

PATTERN	MATCHES
`'[a-c]at'`	Matches `'bat'` and `'cat'` but not `'fat'`.
`'[bcf]at'`	Matches `'bat'`, `'cat'`, and `'fat'` but not `'eat'`.
'`[^c]at'`	Matches `'bat'` and `'fat'` but not `'cat'`.
`'se[^n]%'`	Matches strings of length `≥ 2` that begin with se and whose third character isn’t n.

Some DBMSs let you use LIKE to search numeric and datetime columns.

< Back Page 7 of 10 Next >

🔖 Save To Your Account

Peachpit Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from Peachpit and its family of brands. I can unsubscribe at any time.

Privacy Notice

Email Address

SQL Visual QuickStart Guide: Retrieving Data from a Table

This chapter is from the book

This chapter is from the book

This chapter is from the book 

Matching Patterns with LIKE

Table 4.7. Wildcard Operators

Table 4.8. Examples of % and _ Patterns

Listing 4.30. List the authors whose last names begin with Kel. See Figure 4.30 for the result.

Figure 4.30 Result of Listing 4.30.

Listing 4.31. List the authors whose last names have ll (el-el) as the third and fourth characters. See Figure 4.31 for the result.

Figure 4.31 Result of Listing 4.31.

Listing 4.32. List the authors who live in the San Francisco Bay Area. (Zip codes in that area begin with 94.) See Figure 4.32 for the result.

Figure 4.32 Result of Listing 4.32.

Figure 4.33 Result of Listing 4.33.

Table 4.9. Escaped and Unescaped Patterns

Listing 4.34. List the titles that contain percent signs. Only the % that follows the escape character ! has its literal meaning; the other two percent signs still act as wildcards. See Figure 4.34 for the result.

Figure 4.34 Result of Listing 4.34. An empty result. No title names contain a % character.

Table 4.10. Examples of [] and [^] Patterns

Peachpit Promotional Mailings & Special Offers

Matching Patterns with `LIKE`

Listing 4.34. List the titles that contain percent signs. Only the `%` that follows the escape character `!` has its literal meaning; the other two percent signs still act as wildcards. See Figure 4.34 for the result.

Figure 4.34 Result of Listing 4.34. An empty result. No title names contain a `%` character.