MySQL REGEXP ip address, zip codes, email

Here are some sample mysql regular expressions. This can be useful in looking through text fields for structured data like ip addresses, email addresses or other data.

IP Address

SELECT * FROM data
WHERE field RLIKE '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}';

Zip Code

SELECT * FROM directory
WHERE zip REGEXP '^[0-9]{5}$';

Email Address

SELECT * FROM notes
WHERE note RLIKE '[A-Z0-9._-]+@[A-Z0-9.-]+\.[A-Z]{2,4}';

In MySQL, '=', LIKE and RLIKE are all case insensitive. To do a case sensitive match:

SELECT * FROM notes WHERE note LIKE BINARY '%Jacob%';
SELECT * FROM notes WHERE note RLIKE BINARY '[Jj]acob';

code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)

zedwood.com

MySQL REGEXP ip address, zip codes, email