Pattern Matching with Strings - Pattern Matching with Regular Expressions continued

(Page 2 of 5 )

Regular expressions do not match NULL values. This is true both for REGEXP and for NOT REGEXP:

  mysql> SELECT NULL REGEXP '.*', NULL NOT REGEXP '.*';

NULL REGEXP '.*' | NULL NOT REGEXP '.*'

 

NULL NULL

The fact that a regular expression matches a string if the pattern is found anywhere in the string means you must take care not to inadvertently specify a pattern that matches the empty string. If you do, it will match any non-NULL value. For example, the pattern a* matches any number of a characters, even none. If your goal is to match only strings containing nonempty sequences of a characters, use a+ instead. The + requires one or more instances of the preceding pattern element for a match.

As with SQL pattern matches performed using LIKE, regular expression matches performed with REGEXP sometimes are equivalent to substring comparisons. The ^ and $ metacharacters serve much the same purpose as LEFT() or RIGHT(), at least if you’re looking for literal strings:

Pattern match	Substring comparison
str REGEXP '^abc'	LEFT(str,3) = 'abc'
str REGEXP 'abc$'	RIGHT(str,3) = 'abc'

For nonliteral strings, it’s typically not possible to construct an equivalent substring comparison. For example, to match strings that begin with any nonempty sequence of digits, you can use this pattern match:

  str REGEXP '^[0-9]+'

That is something that LEFT() cannot do (and neither can LIKE, for that matter).

A limitation of regular expression (REGEXP) matching compared to SQL pattern (LIKE) matching is that REGEXP works only for single-byte character sets. It cannot be expected to work with multibyte character sets such as utf8 or sjis.

Next: 5.12 Controlling Case Sensitivity in Pattern Matching >>
 

More Database Articles Articles
More By O'Reilly Media

Comments On: Pattern Matching with 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.