- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 KellseyListing 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 KellseyListing 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 94305Listing 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.