WebProNews

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.

Tag:

Add to Del.icio.us | Digg | Reddit | Furl

Bookmark WebProNews:

Raymond Camden, ray@camdenfamily.com
http://ray.camdenfamily.com

Raymond Camden is Vice President of Technology for roundpeg, Inc. A long
time ColdFusion user, Raymond has worked on numerous ColdFusion books
and is the creator of many of the most popular ColdFusion community web
sites. He is an Adobe Community Expert, user group manager, and the
proud father of three little bundles of joy.