index.md (3218B)
1 --- 2 title: "Regular Expressions in MySQL" 3 date: 2011-09-28T00:00:00+00:00 4 draft: false 5 canonical_url: https://www.viget.com/articles/regular-expressions-in-mysql/ 6 --- 7 8 Did you know MySQL supports using [regular 9 expressions](https://en.wikipedia.org/wiki/Regular_expression) in 10 `SELECT` statements? I'm surprised at the number of developers who 11 don't, despite using SQL and regexes on a daily basis. That's not to say 12 that putting a regex into your SQL should be a daily occurrence. In 13 fact, it can [cause more problems than it 14 solves](https://en.wikiquote.org/wiki/Jamie_Zawinski#Attributed), but 15 it's a handy tool to have in your belt under certain circumstances. 16 17 ## Basic Usage 18 19 Regular expressions in MySQL are invoked with the 20 [`REGEXP`](http://dev.mysql.com/doc/refman/5.1/en/regexp.html) keyword, 21 aliased to `RLIKE`. The most basic usage is a hardcoded regular 22 expression in the right hand side of a conditional clause, e.g.: 23 24 ```sql 25 SELECT * FROM users WHERE email RLIKE '^[a-c].*[0-9]@'; 26 ``` 27 28 This SQL would grab every user whose email address begins with 'a', 'b', 29 or 'c' and has a number as the final character of its local portion. 30 31 ## Something More Advanced 32 33 The regex used with RLIKE does not need to be hardcoded into the SQL 34 statement, and can *in fact* be a column in the table being queried. In 35 a recent project, we were tasked with creating an interface for managing 36 redirect rules à la 37 [mod_rewrite](http://httpd.apache.org/docs/current/mod/mod_rewrite.html). 38 We were able to do the entire match in the database, using SQL like this 39 (albeit with a few more joins, groups and orders): 40 41 ```sql 42 SELECT * FROM redirect_rules WHERE '/news' RLIKE pattern; 43 ``` 44 45 In this case, '/news' is the incoming request path and `pattern` is the 46 column that stores the regular expression. In our benchmarks, we found 47 this approach to be much faster than doing the regular expression 48 matching in Ruby, mostly because of the lack of ActiveRecord overhead. 49 50 ## Caveats 51 52 Using regular expressions in your SQL has the potential to be slow. 53 These queries can't use indexes, so a full table scan is required. If 54 you can get away with using `LIKE`, which has some regex-like 55 functionality, you should. As always: benchmark, benchmark, benchmark. 56 57 Additionally, MySQL supports 58 [POSIX](https://en.wikipedia.org/wiki/POSIX) regular expressions, not 59 [PCRE](http://www.pcre.org/) like Ruby. There are things (like negative 60 lookaheads) that you simply can't do, though you probably ought not to 61 be doing them in your SQL anyway. 62 63 ## In PostgreSQL 64 65 Support for regular expressions in PostgreSQL is similar to that of 66 MySQL, though the syntax is different (e.g. `email ~ '^a'` instead of 67 `email RLIKE '^a'`). What's more, Postgres contains some useful 68 functions for working with regular expressions, like `substring` and 69 `regexp_replace`. See the 70 [documentation](http://www.postgresql.org/docs/9.0/static/functions-matching.html) 71 for more information. 72 73 ## Conclusion 74 75 In certain circumstances, regular expressions in SQL are a handy 76 technique that can lead to faster, cleaner code. Don't use `RLIKE` when 77 `LIKE` will suffice and be sure to benchmark your queries with datasets 78 similar to the ones you'll be facing in production.