Validating email addresses in MySQL with regular expressions

Stand back everyone! I know regex!

I just created a function in MySQL that attempts to validate an email address with the use of regular expressions. Here it is:

DELIMITER $$

DROP FUNCTION IF EXISTS `VALIDATE_EMAIL`$$
CREATE FUNCTION `VALIDATE_EMAIL` (email VARCHAR(360)) RETURNS INT
BEGIN
RETURN email REGEXP "^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@([a-z0-9]+[a-z0-9-]*)*[a-z0-9]+(\.([a-z0-9]+[a-z0-9-]*)*[a-z0-9]+)*\.[a-z]{2,6}$";
END$$

DELIMITER ;

Basically it checks that the email does not begin with a “.”, contains an @ in the middle and that each domain part can only begin and end with a-z0-9 but may have – in the middle and finally that the rightmost part is “.” and 2-6 characters of a-z.

Use like so: SELECT VALIDATE_EMAIL('your.email@domain.tld');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s