Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
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
              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
    
    ----------------------------------------
  • + Share This
  • 🔖 Save To Your Account