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.
Yeah, the regex is a lot slower than using LIKE!
I just tested on a large dataset and using like was around 0.9 seconds, using regular expressions to do the same thing took 7 seconds!
Still could come in handy some day though.
This sounds a good use case for a nightly process rather a transactional query. I can see the need for this in a stored procedure for some sort of compilation of aggregation process.
I can also see it being darn useful for ad hoc queries.