• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Query to filter out "free" email addresses

Hi Experts,

I want to create a query that will filter out "free" email addresses using domains such as hotmail.com.

Assume I have a Table called "Free Email Domain Names", with sample records like this:

strFreeDomainName
hotmail.com
yahoo.com
mail.com

Also assume I have a Table of email addresses, "Emails", with sample records

strEmail
bob@hotmail.com
jack@yahoo.com
real.person@somecompany.com

The query should return:

real.person@somecompany.com


Regards,
Leigh
0
LeighWardle
Asked:
LeighWardle
  • 3
  • 3
1 Solution
 
Pratima PharandeCommented:
select strEmail from Email_address

where Substring(strEmail,CharIndex('@',strEmail)+1,Len(strEmail) not in (Select strFreeDomainName from Free_Email_Domain_Names)
0
 
LeighWardleAuthor Commented:
Thanks pratima_mcs,

I am getting a syntax error:


Missing ), ], or Item in query expression 'Substring(strEmail,CharIndex('@',strEmail)+1,Len(strEmail) not in (Select
             strFreeDomainName from Free_Email_Domain_Names)'.

Regards,
Leigh


               


0
 
Pratima PharandeCommented:
select strEmail from Email_address

where Substring(strEmail,CharIndex('@',strEmail)+1,Len(strEmail)) not in (Select strFreeDomainName from Free_Email_Domain_Names)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LeighWardleAuthor Commented:
Thanks pratima_mcs,

Now I am getting this error:

Undefined function 'Substring' in expression.

I am running MS Access 2003.

Regards,
Leigh
0
 
Pratima PharandeCommented:
select strEmail from Email_address
where Mid(strEmail,Instr(1,strEmail,'@')+1,Len(strEmail)-Instr(1,strEmail,'@') ) not in (Select strFreeDomainName from Free_Email_Domain_Names)
0
 
LeighWardleAuthor Commented:
Thanks pratima_mcs,

Regards,
Leigh
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now