Many months ago I reviewed Ben's MySQL Crash Course, an excellent book that discusses MySQL at a very high, quick to read level. One of the cool things I discovered was that MySQL supports regex in SQL queries. How about some examples....

Consider a typical OR style search like the query below:

select id from products where name like '%alpha%' or name like '%beta%'

By using the regex support in MySQL, you can rewrite it as:

select id from products where name regexp '(alpha|beta)'

MySQL doesn't support the full set of regular expressions you use in ColdFusion or Perl, but it does support most of what you would use normally. That includes beginning and end of line matches, character classes, ranges, and matching certain numbers of items.

A few more quick notes: The MySQL Regex escape character is two back slashes. So for example, \. will escape the . character. Secondly - to do case sensitive regular expressions, you use the binary keyword:

select id from products where name regexp binary 'Camden'

Lastly - do know that when you use regex in MySQL, the engine has to check each and every line to see if your regex matches. This may lead to slower performance.