Wildcards in subquery

Results 1 to 3 of 3

Thread: Wildcards in subquery

  1. #1
    Joe G Guest

    Default Wildcards in subquery

    Hello, <BR>I am trying to create a query that excludes certain domains from email address data in a table. I have two tables, one that has all of the email address(tbl_emails), and another that lists all of the domains to exclude(tbl_exclusion). What would be the syntax for a stored procedure that will use the domains (aol.com, msn.com, etc) in the second table as input for a query like <BR>"select email from tbl_emails where email not in(select domain from tbl_exclusion)"(this doesn&#039;t seem to do it)<BR>I am using SQL Server 7.0. I&#039;m trying to make this so that if a new domain is to be excluded it only needs to be added to the exclusion table. So if aol.com and msn.com are added to the exclusion list, all aol.com and msn.com email addresses will not be included in the results.<BR>Kind of like "select email from tbl_emails where email not like &#039;%@aol.com&#039; and email not like &#039;%msn.com&#039;"<BR> except dynamic in a stored procedure. Is it posible, or is there a better way to do this.

  2. #2
    Join Date
    Dec 1969

    Default RE: Wildcards in subquery

    select email from tbl_emails where email not in(select domain from tbl_exclusion)<BR><BR>my guess is you need to compare the domain of the email<BR>instead of the entire email but your syntax is correct (at least i think it is)<BR><BR>you may have to use charindex and then substring to capture the domain of each email dynamically.<BR><BR>Substring(email,CharIndex(emai l,&#039;@&#039;),length of string left)<BR><BR>needs some work, but its a start.

  3. #3
    Joe G Guest

    Default RE: Wildcards in subquery

    Thanks,<BR> I ended using the following:<BR>select email from tbl_emails where Substring(email,charindex(&#039;@&#039;, email) + 1,len(email) - charindex(&#039;@&#039;, email)) not in(select domain from tbl_exclude)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts