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.

2 comments:

  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 ='192.168.0.133' or ip_address='192.168.0.135'
    is quicker if ip_address is indexed. Obviously, more complex regexp cannot be converted easily...

    ReplyDelete
  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.
    Cheers

    ReplyDelete