Friday, 13 November 2009

mysql and regular expressions

The first time I needed to have some kind of complex select statement, I started to pray about the ability to use regular expressions with mysql.

Easy as it may be: use REGEXP!

For example:

SELECT * FROM db.ip_addresses WHERE ip_address REGEXP '^192\.168\.0\.(133|135)$';

See the official documentation and details here.


  1. hey! nice blog :) I've quit my weblogging activity, but I'm posting a lot on flickr :)

    Anyway, I think regexp is quite slow, perhaps ... where ip_address ='' or ip_address=''
    is quicker if ip_address is indexed. Obviously, more complex regexp cannot be converted easily...

  2. Thanks for your comment.
    Performance has been my first concern, although I haven't found so far a clear documentation on possible impacts.
    Given that yes, it's probably not worth using a regexp for just 2 IP addresses, in my case the mysql statement is 'generated' by some other code and configuration files, so using that approach gives me the impression of higher flexibility.