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';