Adil's blog Blog about web-development.

Regular expressions in SQL queries

Regular expressions are useful for parsing string, matching patterns, validating etc. If we talk about strings so, I think it's good idea using it in SQL queries.

Let we have table users:

mysql> SELECT * FROM users;
+----+-----------+----------+----------------------+
| id | first     | last     | email                |
+----+-----------+----------+----------------------+
|  1 | John      | SMITH    | jsmith@example.az    |
|  2 | Bill      | Gates    | bgates@example.ru    |
|  3 | Steve     | Jobs     | sjobs@example.de     |
|  4 | Sylvester | Stallone | stallone@example.com |
|  5 | Chuck     | Norris   | cnorris@example.net  |
|  6 | Bruce     | Lee      | blee@example1.net    |
+----+-----------+----------+----------------------+
6 rows in set (0.00 sec)

Now we want to get users whose email ends with three-letter TLD (.com,.net,.org etc). We can do this as follows:

mysql> SELECT * FROM users WHERE email LIKE '%.___';
+----+-----------+----------+----------------------+
| id | first     | last     | email                |
+----+-----------+----------+----------------------+
|  4 | Sylvester | Stallone | stallone@example.com |
|  5 | Chuck     | Norris   | cnorris@example.net  |
|  6 | Bruce     | Lee      | blee@example1.net    |
+----+-----------+----------+----------------------+
3 rows in set (0.00 sec)

Well.

What can we do, if we need get users whose email ends with digit dot three-letter TLD(blee@example1.net)? ooops.

I'll not lie, I don't know how to do it with ANSI SQL and I'll use RDBMS specific functions. This example works for MySQL:

mysql> SELECT * FROM users WHERE email REGEXP '^.+[0-9]\.[a-z]{3}$';
+----+-------+------+-------------------+
| id | first | last | email             |
+----+-------+------+-------------------+
|  6 | Bruce | Lee  | blee@example1.net |
+----+-------+------+-------------------+
1 row in set (0.00 sec)

For Oracle it will as follow:

SELECT * FROM users WHERE REGEXP_LIKE(email,'^.+[0-9]\.[a-z]{3}$')

Thanks for my co-worker Mahir for the idea

Reference:
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
http://dev.mysql.com/doc/refman/5.1/en/regexp.html