Managing & Using MySQL, 2nd Edition

Pattern Matching

We provided a peek at ANSI SQL pattern matching earlier with the query:

SELECT name FROM people WHERE name LIKE 'Stac%'

Using the LIKE operator, we compared a column value (name) to an incomplete literal ('Stac%'). MySQL supports the ability to place special characters into string literals that match like wild cards. The % character, for example, matches any arbitrary number of characters, including no character at all. The above SELECT statement would therefore match Stacey, Stacie, Stacy, and even Stac. The character _ matches any single character. Stac_y would match only Stacey. Stac__ would match Stacie and Stacey, but not Stacy or Stac.

Pattern-matching expressions should never be used with the basic comparison operators. Instead, they require the LIKE and NOT LIKE operators. It is also important to remember that these comparisons are case-insensitive except on binary columns.

MySQL supports a non-ANSI kind of pattern matching that is actually much more powerful using the same kind of expressions to which Perl programmers and grep users are accustomed. MySQL refers to these as extended regular expressions. Instead of LIKE and NOT LIKE, these operators must be used with the REGEXP and NOT REGEXP operators. MySQL provides synonyms for these: RLIKE and NOT RLIKE. Table 3-7 contains a list of the supported extended regular expression patterns.

Table 3-7. MySQL extended regular expressions

Pattern	Description	Examples
.	Matches any single character. ...