Working with Cases of Strings - 5.10 Pattern Matching with SQL Patterns

Problem

You want to perform a pattern match rather than a literal comparison.

Solution

Use the LIKE operator and an SQL pattern, described in this section. Or use a regular-expression pattern match, described in Recipe 5.11.

Discussion

Patterns are strings that contain special characters. These are known as metacharacters because they stand for something other than themselves. MySQL provides two kinds of pattern matching. One is based on SQL patterns and the other on regular expressions. SQL patterns are more standard among different database systems, but regular expressions are more powerful. The two kinds of pattern match uses different operators and different sets of metacharacters. This section describes SQL patterns. Recipe 5.11 describes regular expressions.

The example here uses a table named metal that contains the following rows:

  +----------+
  | name     |
  +----------+
  | copper   |
  | gold     |
  | iron     |
  | lead     |
  | mercury  |
  | platinum |
  | silver   |
  | tin      |
  +----------+

SQL pattern matching uses the LIKE and NOT LIKE operators rather than = and != to perform matching against a pattern string. Patterns may contain two special metacharacters: _ matches any single character, and % matches any sequence of characters, including the empty string. You can use these characters to create patterns that match a variety of values:

• Strings that begin with a particular substring:
  
     mysql> SELECT name FROM metal WHERE name LIKE 'co%';
    +--------+
    | name   |
    +--------+
    | copper |
    +--------+ 
  
• Strings that end with a particular substring:
  
    mysql> SELECT name FROM metal WHERE name LIKE '%er';
    +--------+
    | name   |
    +--------+
    | copper |
    | silver |
    +--------+ 
  
• Strings that contain a particular substring at any position:
  
    mysql> SELECT name FROM metal WHERE name LIKE '%er%';
    +---------+
    | name    |
    +---------+
    | copper  |
    | mercury |
    | silver  |
    +---------+ 
  
• Strings that contain a substring at a specific position (the pattern matches only if pp occurs at the third position of the name column):
  
    mysql> SELECT name FROM metal where name LIKE '__pp%';
    +--------+
    | name   |
    +--------+
    | copper |
    +--------+

An SQL pattern matches successfully only if it matches the entire comparison value. Of the following two pattern matches, only the second succeeds:

  'abc' LIKE 'b'
  'abc' LIKE '%b%'

To reverse the sense of a pattern match, use
NOT LIKE. The following statement finds strings that contain no i characters:

  mysql> SELECT name FROM metal WHERE name NOT LIKE '%i%';
  +---------+
  | name    |
  +---------+
  | copper  |
  | gold    |
  | lead    |
  | mercury |
  +---------+

SQL patterns do not match NULL values. This is true both for LIKE and for NOT LIKE:

  mysql> SELECT NULL LIKE '%', NULL NOT LIKE '%';
  +---------------+-------------------+
  | NULL LIKE '%' | NULL NOT LIKE '%' |
  +---------------+-------------------+
  |          NULL |              NULL |
  +---------------+-------------------+

Unlike some other database systems, MySQL allows pattern matches to be applied to nonstring values such as numbers or dates, which can sometimes be useful. The following table shows some ways to test a DATE value d using function calls that extract date parts and using the equivalent pattern matches. The pairs of expressions are true for dates occurring in the year 1976, in the month of April, or on the first day of the month:

Function value test	Pattern match test
YEAR(d) = 1976	d LIKE '1976-%'
MONTH(d) = 4	d LIKE '%-04-%'
DAYOFMONTH(d) = 1	d LIKE '%-01'

In some cases, pattern matches are equivalent to substring comparisons. For example, using patterns to find strings at one end or the other of a string is like using LEFT() or RIGHT():

Pattern match	Substring comparison
strLIKE 'abc%'	LEFT(str,3) = 'abc'
strLIKE '%abc'	RIGHT(str,3) = 'abc'

If you’re matching against a column that is indexed and you have a choice of using a pattern or an equivalent LEFT() expression, you’ll likely find that the pattern match is faster. MySQL can use the index to narrow the search for a pattern that begins with a literal string. With LEFT(), it cannot.

Please check back next week for the continuation of this article.

Comments On: Working with Cases of Strings

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.