Filtering out invalid email addresses

[#1]

Morning, I cant get the script below to produce the result I want, on my system at work you can't have a blank email address field for customer so we put N@ or n@. So I wrote a script below to exclude these for an audit.

Table1:

LOAD Standard_Sender_Address,

Email_Address

FROM

(qvd)

where Standard_Sender_Address='X'

or (left(Email_Address,2)<>'N@')

or (left(Email_Address,2)<>'n@')

;

This is still giving me the the emails I dont want.

i'm a bit baffled as this looks like the correct way to do it.

Paul.

[Miguel_Angel_Baeyens]

Hi,

Do you mean you don't want to load all email addresses from the QVD file that start with "N@"? If so, the following should work:

WHERE Upper(Left(Email_Address, 2)) <> 'N@';

Hope that helps.

Miguel

[CELAMBARASAN]

Hi,

     In your way it should be

Table1:

LOAD Standard_Sender_Address,

Email_Address

FROM

(qvd)

where Standard_Sender_Address='X'

or (left(Email_Address,2)<>'N@'

and left(Email_Address,2)<>'n@')

;

Its better to use upper or Lower functions to make case insensitive comparison

where Standard_Sender_Address='X'

or Upper(left(Email_Address,2))<>'N@'

;

Or

where Standard_Sender_Address='X'

or Lower(left(Email_Address,2))<>'N@'

;

or use wildmatch

where Standard_Sender_Address='X'

or not WildMatch(Email_Address,'N@*')

;

or use MixMatch

where Standard_Sender_Address='X'

or MixMatch(left(Email_Address,2)),'N@')

;

I suggest you to use wildMatch.

Celambarasan

[#4 · Accepted solution]

I fiinally figured it out! The script below will help filter out unwanted email addresses where people have filled in a free format mix of n/a,n@a or ask (change it relevent to the data on your specific system)

Table1:

LOAD Standard_Sender_Address,

Email_Address

FROM

(qvd)

where Standard_Sender_Address='X'

and not WildMatch(Email_Address,'N@*')

and not WildMatch(Email_Address,'N'&chr(39)&'@*') This filters out email addresses with n’@* in it

and not WildMatch(Email_Address,'ask*')

and not WildMatch(Email_Address,'n/a*')

and not WildMatch(Email_Address,'na@*')

and lower(right(Email_Address,3))<>'n/a'