Deleting false emails from a database

[webboy1]

Hi,

Does anyone know how to find and delete bad emails in a database. I know i can do a search for emails where the email NOT LIKE '%@%'.

I guess i could also do a search for those NOT lIKE '%.com', but that would also delete the .co.uk's, the .net's etc.

Does anyone know of a good way to do this, or is it really just a case of going through checking myself 1 by 1?

Webboy

[wardbekker]

webboy1,

You could:

Loop trough all records
Use a regular expression that validates the e-mail adres
If invalid -> delete record

[magicsoftware]

i have a short java class that validates an email address. it will read an email address and will return either true or false, by:
1. checking for the existance of @ and .
2. checking for invalid chars - only letters, digits, underscore, dots - or @ are allowed

you could make it run in a loop down your sql table and delete the row if not validated.
if you're interested i'll sticky mail it to ya.

[wardbekker]

magicsoftware,

a regular expression like this : ^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)¦(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}¦[0-9]{1,3})(\]?)$ would find virtual all errounous e-mail adresses. Unfortunately, just testing for invalid chars is not sufficient enough.

For example

joe.cool@hotmailcom shouldn't be accepted

[webboy1]

Hi,

Thanks for the ideas.

I already know a few ways to filter the emails before they go into the database, but i have been given a Database table, which has a some bad emails.

So i was more looking for some SQL Script (if any) to delete most (if not all) of these.

Thanks Anyway

Webboy

[BradleyT]

Read in the emails from the SQL Server through a loop.

Perform regular expression check on them.

If bad - put in a new "bad email" table.

Print/Scan the "bad email" table for manual verification. You wouldn't want to delete on the first run in case you had a mistake in your code and deleted valid emails.

Once the bad email table has been verified manually, write a script that deletes from "big email" table where bigemail.emailaddress = bademail.emailaddress.

Really pretty easy to do.