Email Validation

Question

Email Validation

joemai

Hall of Fame

Points: 3496
More actions
June 19, 2007 at 4:12 pm

#153640

Can you please show me a way to validation email? I have a list of email with a lot of bad formed emails like '%[mvu@yahoo.com]'. Is there a function to clean those emails up?
Thanks in advance for all of your help.
Minh Vu

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Wayne West SSC-Insane Points: 22586 More actions · Answer 1

Are you looking to validate email at entry time, or clean up a table? This is not an ideal solution, but it shows the process. I'm sure someone will post a better solution.

(the Lower() is important because we don't have to test against upper case alpha characters)

declare @badaddr varchar(200)

declare @goodchars char(40) --valid characters for email addresses

declare @addrlen tinyint --length of addr to inspect

declare @tchar char(1) --character being inspected

set @badaddr = '%[mvu@yahoo. COM]'

set @badaddr = lower(@badaddr)

set @goodaddr = ''

set @goodchars = '1234567890@._-abcdefghijklmnopqrstuvwxyz'

set @addrlen = len(rtrim(@badaddr))

while (@addrlen > 0)

begin

set @tchar = substring(@badaddr, @addrlen, 1)

if charindex(@tchar, @goodchars, 1) = 0 --bad character, remove it

set @badaddr = replace(@badaddr, @tchar, '')

set @addrlen = @addrlen - 1

end

print @badaddr

I know there is a better way to do this, but I'm tired and hungry and can't find it off-hand.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

joemai Hall of Fame Points: 3496 More actions · Answer 2

I tried to clean up the table because the email list I got from some outside source.

I used the below where clause to eliminate most of the bad emails:

where ( like '[a-zA-Z0-9]%@[a-zA-Z0-9]%.%[a-zA-Z0-9]')

and ((not(( like '%[ ]%'))))

and ((not(( like '%@[/\$#!~&(){},;*<>=]%'))))

and ((not(( like '%[[]%'))))

and ((not(( like '%..%'))))

and ((not(( like '%]%'))))

and ((not(( like '%@%@%'))))

But I'm stuck with this type of email:

20070601050354.0CA7D189619F246F@dmonsites.net

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

I think that technically that is a valid format address: it is letters, numbers, one ampersand, a period, and a properly formed site and domain, so there's no reason why it would not have passed your filter. After you've cleaned your list, maybe sort it descending by the length of the address for manual purging?

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]