Email Address Validation

[gerhardl]

Hello,

What would be the best approach if I want to apply email validation in my load script? i.e. I want to create a new field called [Email Valid?], and it must have a YES value if it passes all of the checks and a NO value if it doesn't.

Is there a better way than a complicated IF/AND/OR type statement?

I want to apply these rules:

Address cannot contain the following characters '%[ &'',":;!+=\/()<>]%'

Address cannot start with the following characters '[@.-_]%'

Address cannot end with the following characters '%[@.-_]'

Address must contain at least one @ and one .

Address cannot contain two .. in a row

Address cannot contain two @ anywhere

Address cannot have @ and . next to each other

A .com address cannot end in .cm or.co

A .org address cannot end in .or

A .net address cannot end in .ne

A .za address cannot end in .z

Thanks,

Gerhard

[jonathandienst]

Gerhard

Well you can use regular expressions via vbscript function call, but I don't use those for performance reasons. Search here for "VBScript regular expressions".

Of you can do it IF/AND/OR, but its not too complicated.

If(Len(KeepChar(Address, '%[ &'',":;!+=\/()<>]') = 0)
  And FindOneOf(Left(Address, 1) & Right(Address, 1), '@.-_') = 0
  And SubStringCount(Address, '@') = 1
  And Index(Address, '..') = 0
  And WildMatch(Address, '*@.*', '*.@', '*@@*') = 0
  And (If(Index(Address, '.com') = 0 Or WildMatch(Address, '*.cm', '*.co') = 0))
  And (If(Index(Address, '.org') = 0 Or WildMatch(Address, '*.or') = 0))
  And (If(Index(Address, '.net') = 0 Or WildMatch(Address, '*.ne') = 0))
  And (If(Index(Address, '.za') = 0 Or WildMatch(Address, '*.z') = 0))
  , 'YES', 'NO'
) As [Email Valid?]

Hope that helps

Jonathan

[gerhardl]

Thanks Jonathan,

I had to make a few changes to make it work for me (my question and rule set was not perfectly clear - apologies).

  If([Email address] ='','None',

  If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

  And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

  And (SubStringCount([Email address], '@') = 1)

  And (Index([Email address], '..') = 0)

  And (WildMatch([Email address], '*@.*', '*.@', '*@@*') = 0)

  And (right([Email address],3)<>'.cm' )

  And (right([Email address],3)<>'.co' )

  And (right([Email address],3)<>'.or' )

  And (right([Email address],3)<>'.ne')

  And (right([Email address],2)<>'.z'), 'Valid', 'Invalid')) As [Email Valid?],

[jonathandienst]

Gerhard

Then we can simplify it further:

If(Len([Email address]) = 0,

     'None',

     If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

          And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

          And (SubStringCount([Email address], '@') = 1)

          And (WildMatch([Email address], '*..*','*@.*', '*.@', '*@@*', '*.cm', '.co', '.or', '.ne', '.z') = 0)

          , 'Valid', 'Invalid'

     )

) As [Email Valid?],

Jonathan

[gerhardl]

Hi Jonathan,

I now have a mismatch between mine and yours - they are emails ending with .co or .z or .ne

I have them as invalid and yours is valid - any idea why?

Also - the rule for them must be that the email cannot END in those. If I use wildmatch it is going to check if it CONTAINS them, which might cause problems.

For instance, this email is valid:  example.cm@gmail.com

but this is invalid: example.cm@gmail.cm

Also, South African email addresses end is .co.za   - so '.co' cannot be seen as invalid unless it is at the very end.

[jonathandienst · Accepted solution]

Gerhard

If you are basing this on the first script that I posted, then that only applies the rule to exclude .cm if .com also exists in the email address. The later query should mark these as invalid. as does your script.

Oh, I see that the * is missing for the last 4  values in the last Wildmatch. So corrected is

If(Len([Email address]) = 0,

     'None',

     If((Len(KeepChar([Email address], '%[ &'',":;!+=\/()<>]')) = 0)

          And (FindOneOf(Left([Email address], 1) & Right([Email address], 1), '@.-_') = 0)

          And (SubStringCount([Email address], '@') = 1)

          And (WildMatch([Email address], '*..*','*@.*', '*.@', '*@@*', '*.cm', '*.co', '*.or', '*.ne', '*.z') = 0)

          , 'Valid', 'Invalid'

     )

) As [Email Valid?],

Regards

Jonathan

[gerhardl]

I was talking about your latter query - I completely missed the asterisk as well, working perfectly now. You just need another asterisk after *.@ as well:

And (WildMatch([Email address], '*..*','*@.*', '*.@*', '*@@*', '*.cm', '*.co', '*.or', '*.ne', '*.z')

Thanks for the help.