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

Dissecting traces with DTMF tones

I'm sure I belong to the large group of people who love to analyse network traces with tools like Wireshark. Being able to see the detai...