So after my recent battle with "dirty" email addresses, I decided to find some validating functions to help people and myself in the future. Most of them use regular expressions.
So here they are:
MySQL stored function
Taken from - http://forge.mysql.com/snippets/view.php?id=62
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 ;
In VBA (for excel, access and SQL server) – you need to enable “Microsoft VBScript Regular Expressions 5.5” in the preference
Dim myRegExp As VBScript_RegExp_55.RegExp
Function ValidEmail(ByVal email As String) As Boolean
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = "^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$"
ValidEmail = myRegExp.Test(email)
End Function
In JavaScript
Taken from - http://neural.cs.nthu.edu.tw/jang/sandbox/javascript/examples/chkemail.asp
function CheckEmail(str) {
var testresults=true
var filter=/^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*\.(\w{2}|(com|net|org|edu|int|mil|gov|arpa|biz|aero|name|coop|info|pro|museum))$/
if (filter.test(str)){
var tempstring = str.split("@")
tempstring = tempstring[1].split(".")
} else {
testresults=false
}
return (testresults)
}
In Flash ActionScript
Taken from someone at my work. No regular expression.
private function checkEmail(value:String):Boolean{
var mail1= value.split("@")[0]
var mail2= value.split("@")[1]
var mail2_end = mail2.split(".")[mail2.split(".").length-1]
var lastCharASCII = value.charCodeAt(value.length-1)
if(value.split("@").length!=2) return false
if(mail1=="" || mail2== "" || mail2.split(".").length<2)>3) return false;
if((lastCharASCII<65>122)||(lastCharASCII>90 && lastCharASCII<97))>
Hope it helps.
