mysql regexp to match email addresses?

[Anon]

Anyone have a regexp that I can use to verify that a particular field (email) is a valid email address? We check for this sort of thing now but prior to my being hired, there was no check. =/

Thanks!

[Anon]

---

code from

http://www.planet-source-code.com/xq/ASP/txtCodeId.430/lngWId.8/qx/vb/scripts/ShowCode.htm

function checkEmail($address)
{
if(ereg( "^[^@ ]+@([a-zA-Z0-9\-]+\.)+([a-zA-Z0-9\-]{2}|net|com|gov|mil|org|edu|int)$",$address) ) 
		return true;
	else
		return false;
}

//***********************************
//

// Name: Email Verification
// Description:This is a function that v
// alidates email address using regex. Will
// also check for top level domain names (.
// com, .net, .edu).
// By: Burhan Khalid
//
// Inputs😮ne value, of type string, whi
// ch is the email address that you want to
// check.
//
// Returns:True or False based on the re
// sult of the check. Returns true if the e
// mail address verifies.
//
// Assumes:This code is not fool proof.
// You might want to trim() your email addr
// ess before you send it to the function.
// I designed this to use in concert with m
// y "send me an email" page.
//
// Side Effects😃oes not check for count
// ry specific domains, (ie. .co.uk), altho
// ugh with enough feedback, I might implem
// ent that in a future version.
//
//This code is copyrighted and has

// limited warranties.Please see http://
// www.Planet-Source-Code.com/xq/ASP/txtCod
// eId.430/lngWId.8/qx/vb/scripts/ShowCode.
// htm

//for details.

//***********************************
//

[Anon]

Do you happen to have the syntax for doing this within a sql statement?

Like:

select email from subscribes where regexp "^[@ ]+@([a-zA-Z0-9-]+.)+([a-zA-Z0-9-]{2}|net|com|gov|mil|org|edu|int)$";

I wonder if that would actually work now that I think about it...

[Anon]

The query takes about 3x as long though! =P

Thanks again!

[[deleted]]

uhm... copyright? on a regular expression that does something as common as verifying an email? I hope that is just a standard disclaimer... (or: who does that guy think he is?)

[[deleted]]

Mysql is not particularly good at anything complex.

Anyway, the regexp is not correct.
It does not allow for the current new toplevel domains, and it lets you put characters like backslashes and commas in the name.

[Anon]

Your query actually should be:

select email from subscribes where email regexp "^[@ ]+@([a-zA-Z0-9-]+.)+([a-zA-Z0-9-]{2}|net|com|gov|mil|org|edu|int)$";

Chris Blessing wrote:

Do you happen to have the syntax for doing this within a sql statement?

Like:

select email from subscribes where regexp "^[@ ]+@([a-zA-Z0-9-]+.)+([a-zA-Z0-9-]{2}|net|com|gov|mil|org|edu|int)$";

I wonder if that would actually work now that I think about it...

[Anon]

This regexp might actually work better. I make no promises with speed, but the accuracy should be there. What do you think?

select email from table where email not regexp "^{[0-9a-z_.-]+@(([0-9]{1,3}.){3}[0-9]{1,3}|([0-9a-z][0-9a-z-]*[0-9a-z].)+[a-z]{2,3})$";}

Malaney

[Anon]

One more tweak:

select email from table where email not regexp "^{[0-9a-z_.-]+@([,]|([0-9]{1,3}.){3}[0-9]{1,3}|([0-9a-z][0-9a-z-]*[\,0-9a-z].)+[a-z]{2,4})$"} or email regexp "[,]";