Another handy MySQL function

This time it's a quick function to validate email addresses, based on regexp.
It can be used in a trigger to add data validation, or to check data already in your database that needs a clean up ... really a simple wrapper around a simple regexp query, but it can be helpful.
Here it is:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_valid_email` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `is_valid_email`(p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')
THEN
-- bad data
RETURN FALSE;
ELSE
-- good email
RETURN TRUE;
END CASE;
END $$
 
DELIMITER ;

As per Mushu's comment, this is much cleaner, oops:

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`is_email_valid` $$
CREATE FUNCTION `test`.`is_email_valid` (p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$'
THEN
-- good email
RETURN TRUE;
ELSE
-- bad data
RETURN FALSE;
END CASE;
END $$
 
DELIMITER ; 

Also, if you can move such checks into the application code, it may be a wise thing to do as executing regular expressions can easly consume all CPU resources.

Of course doing checks in the application code is cleaner, but the ability to add them later at database level, or to run them in the database without interaction with external apps is also handy ;-)