pattern matching problem 1

[nzgirl]

Hi All...
I'm wanting to check for invalid email addresses.
the rules I have so far =
SELECT Count(*) FROM FileName where
Email Not Like '%@%.%'
or Email Like '%@%@%'
or Email Like '%.'
or Email is Null
or Email like '% %' (theres a space in there)
and this catches most of them. I'm just wondering if theres a better way to do it...
so that rather than trying to catch all the wrong ones I'm only looking for the right ones...

The following is a regular expression (in PHP I think) for valid email addressses... can I do similar with SQL?
ereg("(.*)@(.*)(\.)(.*)",$string)

I've read a little on PATINDEX but it seems to need % as well so its similar to my current statements..

Any ideas?
Thanks

 

[ClaireHCM]

I think the query you wrote cant really filter all the invalid email address.
If somebody type g~~123@yahoo.com or $$$@hotmail.com
These email address are invalid.But according to the query you wrote.It is a valid email address.

Valid email adress should be composited by a-z , A-Z , 0-9 ,'-' or '_' before @.
After @,there should be a-z,A-Z or 0-9 in between . and not ended by any other symbol other than english letter

Maybe you can use "ascii" to check each char in the email address.But it's going to take a lots of time if you check one by one.

 

[nzgirl]

Thanks for reminding me about the non alpha chars... they shouldn't be too much of a prob -I should be able to screen them using patindex....
any further ideas re regular expressions?

Thanks

 

[tlbroadbent]

Check the following link.

Working with email addresses in SQL Server

http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[nzgirl]

Excellent!! More food for thought!